Occasionally I want to analyze my data by selecting records around a particular date. For example, given a specific date, I might want to select all of the records in the range of seven days before that date to seven days after that date.
DateAdd VBA function allows you to add date or time values to a specified date/ time value. Thus, you can generate new date/time values relative to that value.
Let's assume that you have the data shown in Figure 8-12.
Running the following query prompts the user for two values (
DateArg, which is the date around which the rows will be selected, and
DateInterval, which is the offset from
DateArg that determines the range of dates to be selected):
Figure 8-12. A list of date/time values
SELECT [Chapter8-7].TaskId, [Chapter8-7].StartDateTime, DateArg,DateInterval FROM [Chapter8-7] WHERE [Chapter8-7].StartDateTime Between DateAdd("d",-DateInterval,DateArg) And DateAdd("d",DateInterval,DateArg);
If the user supplies values of
24 Jan 06 and
DateInterval, respectively, you should see the results shown in Figure 8-13.
DateAdd function gives you the ability to add many different types of offset values to compute a new date. In addition to days, you can add years, quarters, months, weeks, hours, minutes, and seconds to a date/time value.
DateAdd function takes three arguments:
interval, number, and ...