Microsoft Office Tutorials and References

In Depth Information

**Performing Date and Time Calculations**

As you can see in Table 2-4, the DateAdd() and DateDiff() functions

allow you to specify an
interval
argument. That argument defines the time

interval used for the calculation.

If you just use plain date arithmetic to subtract two dates, for example, the

difference between the dates automatically displays as the number of
days

between those dates. By using the DateAdd() or DateDiff() function,

you can tell Access to express the difference between the dates in seconds,

minutes, hours, weeks, months, or years — whichever interval provides the

accuracy you need.

To specify a time interval argument in a DateAdd() or DateDiff()

function, you use one of the settings (enclosed in quotation marks) listed in the

left column of Table 2-5.

Table 2-5

Settings for the Interval Argument

in Date/Time Functions

Setting

Description

“d”

Day

“h”

Hour

Book III

Chapter 2

“m”

Month

Minute

“n”

Quarter

“q”

Second

“s”

“w”

Weekday

“ww”

Week

“y”

Day of year

“yyyy”

Year

Take a look at an example of using an interval in a DateDiff()

function. If you don’t use the DateDiff() function at all, the

expression #12/25/2013# – #12/24/2013# returns 1, because there

is one day between those dates, and “day” is the default interval

for subtracting dates. On the other hand, the expression DateDiff

(“h”,#12/24/2013#,#12/25/2013#) returns 24, because the “h”

interval specifies hours, and there are 24 hours between those two dates.