|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
Working with Dates...

 

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

Adding a number of days to 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

 

Adding days to a date - result 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]

Subtracting one date from another

Subtracting one date from another - result 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.

Formatting the calculated field in the Field Properties dialogOn 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…

Extracting they year from a date with the Year() function 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…

Useng the Year() function - result

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…

Using the Year() function with additional criteria
 

Using the Year() function with additional criteria

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

Using the Year() funstion in query criteria

Using the Year() funstion in query criteria - result

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

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…

Using the Month() function in query criteria

Using the Month() function in query criteria - result

Month([Date])=9 displays records for dates in September

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 the month.

Weekday([Date])=4 displays dates which are a Wednesday.

 

^ 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…

Using the DateAdd function

 
Using the DateAdd function - result 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.

^ top
   

 

 

 

 

Hit Counter