Make sure that any fields in your Access database that contain dates have the data type set to Date/Time. This is vital when it comes to sorting, filtering and calculating dates. A date entered into a Text or Memo field 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 perform date calculations. If you have any problems with dates, check out the design view of the table in which the dates are stored.
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. 8/12/12 would return 2012
- Month([Fieldname]) returns the month from a date e.g. 8/12/12 would return 8
- Day([Fieldname]) returns the day of the month from a date e.g. 8/12/12 would return 12
- Weekday([Fieldname]) returns the day of the week form a date e.g. 8/12/12 would return 6 representing Friday. The weekdays numbering from 1 to 7 starting with Sunday.
These functions can be used in a new field 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.
- 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:
- 2008 displays records for dates in the year 2008 only
- 2008 Or 2009 displays records for dates in 2008 or 2009
- >2008 displays records for dates from 2008 to most recent
- Between 2008 and 2011 displays records for dates in the years 2008, 2009, 2010 and 2011
If it is not necessary to see the extracted date 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])=2008 displays records for dates in 2008
- Year([Date])>2008displays records for dates from 2008 to more recent
- 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 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.