O'Reilly logo

Access Data Analysis Cookbook by Wayne S. Freeze, Ken Bluttman

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Adding Time

Problem

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.

Solution

The 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):

A list of date/time values

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 2 for DateArg and DateInterval, respectively, you should see the results shown in Figure 8-13.

Discussion

The 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.

The DateAdd function takes three arguments: interval, number, and ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required