• Access Index
• Printer friendly version
• Related Topics:
  Calculating in Queries

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 are stored.

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…

To add or subtract days from a date...

Create 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 example…

Stay: [Departure Date]-[Arrival Date]

Stay: 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 Date. 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.

On 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 correctly. [back]  

^ top

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…

	Year( ) 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 only. 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 same applies to the Month( ), Day( ) and Weekday( ) functions. For example…

^ top

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… 

• yyyy for year 

• q for quarter (i.e. 3 months) 

• m for month 

• d for day 

• ww for week

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. For example…

 

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 calendar months.

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