Working with Dates in Access Queries
Access has a number of powerful tools to enable specific dates and date
ranges to be specified in criteria. Many tasks can be achieved with simple
calculations, and there are a number of date functions to help in
performing more complex jobs.
Make sure that any fields you have that contain dates know that their
data type is Date/Time. This is vital when it comes to sorting,
filtering and calculating dates. If you enter a date into a Text or
Memo field it will still look like a date to you, but Access will
treat it as a string of text. You won't be able to sort into correct date
order, nor will you be able to calculate. If you have any problems with
dates, check out the design view of the table in which the dates
Simple Date Calculations
When you enter a date into an Access table, Access recognises it as a
date, and checks it against the calendar to make sure it is a possible
date. You'll get an error message if you try to enter an impossible date
such 31st September or 29th February in a non leap year. Then it stores
the date as a number known as the date serial. PCs use the 1900
System to store dates. 1st January 1900 was day 1, 2nd January 1900
was day 2 etc. You don't need to know the serial number of your dates, but
you can make use of it in mathematical calculations.
Here are a few examples…
a new field with an expression that adds (+) or subtracts (-) the required
number from the field containing the original date. For example…
Due Date: [Invoice Date]+60
||Due Date: creates a new field called Due Date
[Invoice Date]+60 takes the date it finds in the Invoice Date
field and adds 60 to its serial number.
The result is automatically displayed as a date.*[note]
To calculate the number of days between two dates
Create a new field with an expression that subtracts the field
containing the earlier date from the field containing the later date. For
Stay: [Departure Date]-[Arrival Date]
creates a new field called Stay
[Departure Date]-[Arrival Date] subtracts the date found in the
field Arrival Date from the date found in the field Departure
The result is automatically displayed as a number.*[note]
Note: If the result fails to display as a date, or
displays a date in the wrong format, switch to design view and click in
the new field column.
the menu choose View > Properties (or right-click the field and
choose Properties from the shortcut menu). Click in the Format
section on the General tab of the Field Properties dialog
box. Click the down-arrow and choose an appropriate format from the list.
Close the dialog box and run the query again to see your dates displayed
Using Date Functions
There are four basic date functions which extract part of a date so
that it may be displayed on its own, in a new field (further refined with
criteria id required), or with additional information as part of the
criteria of the field in which the date itself occurs. These functions are…
- Year([Fieldname]) returns the year from a date e.g. 20/8/99
would return 1999
- Month([Fieldname]) returns the month from a date e.g. 20/8/99
would return 8
- Day([Fieldname]) returns the day of the month from a date
e.g. 20/8/99 would return 20
- Weekday([Fieldname]) returns the day of the week form a date
e.g. 20/8/99 would return 6 representing Friday.
The weekdays numbering from 1 to 7 starting with Sunday.
Use these functions in a new field if you want to display the extracted
data in addition to the original date. Remember to type the name of the
new field first followed by a colon (:). When you do this you can further
refine the query by entering criteria in the new field's criteria row…
This function is used to extract the year from particular date.
In this example, the function is used simply to display the year
in a new field…
|Year : Year([Date])
creates a new field called Year containing data
calculated from the field [Date].
If no criteria are defined then all the records are
displayed. The usual criteria for defining numbers can be used to
display specific years or ranges. For example…
|1996 displays records for dates in the year
1996 Or 1997 displays records for dates in 1996 or 1997.
>1997 displays records for dates from 1998 onwards.
Between 1996 and 1999 displays records for dates in the
years 1996, 1997, 1998 and 1999.
If you don't need to see the extracted data
separately, you can enter the function as part of the criteria of
the original date field...
|The Year( ) function does not have to be
used in a separate field. It can form part of the criteria
definition. For example…
Year([Date])=1997 displays records for dates in 1997
Year([Date])>1995 displays records for dates from 1995
Remember that you still have to include the name of the field (in
this case the field is called [Date]) within the function,
even though the criteria are typed in that field's column.
The same applies to the Month( ), Day( ) and
Weekday( ) functions. For example…
|Month([Date])=9 displays records for dates in
Month([Date]) Between 4 and 8 displays records for dates
in April, May, June, July and August
Day([Date])=15 displays dates which are the 15th day of
Weekday([Date])=4 displays dates which are a Wednesday.
Advanced Date Functions
Access contains a number of more sophisticated date
functions for when you can't get the result you need using a simple
calculation or one of the basic date functions. Here's an example...
DateAdd(interval, number, [Fieldname])
Use this function to add (or subtract) a specific
amount of time to a date. The interval part of the function
refers to the type of time unit you want to add and requires you to
enter a code…
The number part of the function refers to how
many of those units you want to add. You can use a minus number if
you want to subtract from the date. Finally you need to supply the
name of the field containing the original date.
Adding or subtracting days or weeks can be performed
with simple mathematical calculations as demonstrated above. The DateAdd
function makes it easy to add years, months or quarters to a date.
||Date Due: DateAdd("m",2,[Date])
This expression creates a new field called Date Due into
which it enters dates from the Date field to which it adds 2
This query could be further refined to display
records whose calculated dates fall in a specific range by entering
criteria in the new field's criteria row. For example, if these were
due dates for invoices where the payment terms were one calendar
month, entering <#15/8/99# in the criteria would display
only those invoices for which payment was due before 15th August