Microsoft Office Tutorials and References
In Depth Information
Performing Date and Time Calculations
The ExtendedDate calculated field
adds 15 days to whatever date is stored in the EndDate field.
The bottom half of Figure 2-9 shows the query results in Datasheet view. The
DaysBetween column shows the number of days between the StartDate
value and the EndDate value. The ExtendedDate column shows the date
15 days after the EndDate value, as specified by the expression in the
Using literal dates and times in expressions
When writing expressions that include dates, you can use a literal date, as
opposed to the name of a field that contains a Date/Time value. A literal date
is one that isn’t stored in some field; it’s just a specific date you want to use
in the expression. You can’t just type the date in an everyday format like
12/31/2012, however, because Access interprets that entry as “12 divided
by 31, divided by 2012.” Also, you can’t use quotation marks, which define
literal text. Instead, you have to use the awkward # character to delimit (sur-
round) a literal date.
#01/01/2013# is literally the date January 1, 2013. The expression
#01/01/2013# + 14 returns 1/15/2013, the date that’s 14 days after
January 1, 2013. The expression #3/31/2013# – #1/1/2013# returns 89
because March 31, 2013, is 89 days after January 1, 2013.
To express a literal time, use colons (:) to separate the hours, minutes, and
seconds between the # delimiters. You can also tack on a blank space
followed by AM or PM. #7:30:00# is literally 7:30 AM, as is #7:30:00 AM#.
The literal time #7:30:00 PM# refers to 7:30 at night. You can use military
time as well: The literal time #19:30:00# is also 7:30 PM.
Using the Date/Time functions
You’re not limited to basic date arithmetic in Access. Quite a few built-in
Date/Time functions exist in Access, and you can use them to manipulate
dates and times in other ways. Like all built-in functions, the Date/Time
functions are available in Expression Builder. Again, if the Functions folder in the
left column has a + next to it, click that + to expand the list. Next, click the
Built-In Functions subfolder in the left column and the Date/Time category in
the center column. Then click any function’s name in the right column, and
click the Help button for details on the function.
We spare you the details of every available Date/Time function. Chances are
that you’ll never need to use the most obscure of these functions. Table 2-4
lists some of the most commonly used Date/Time functions and provides
examples of their use.