dates in access

Working with Dates

Dates behave the same way as numbers, so you can use some of the same techniques when constructing your date query or filter. Remember, for dates to be treated properly by Access it is important that your field type has been correctly defined as a Date/Time field. It doesn't matter how you enter the date, as long as you use a recognised format. The date will be displayed in the resulting dynaset in whatever format you chose when you created the table.
When you enter a date in the criteria cell you can use any standard date format, but each date must be enclosed by hash marks (#).
For example:

• <#1/1/98# finds dates earlier than 1 January 1998 
• =#27-Sep-50# finds dates equal to 27 September 1950 
• Between #5/7/98# And #10/7/98# finds dates no earlier than 5 July 1998 and no later than 10 July 1998

Here are some more examples…
=#Date#
To match a particular date type the date enclosed by hash marks (#). This example will display all the records with entries for 27 September 1998 in the Invoice Date field.


=Date()
To match today's date type the expression shown. Date() means "today". This example will display all the records with entries for the current date in the Invoice Date field.


Year([Fieldname])=Year(Now())
To match the current year type the expression shown, entering the name of the current field in square brackets where indicated. This example will display all the records with entries for the current year in the Invoice Date field.

Year([Fieldname])=Year
To match a particular year type the expression shown, entering the name of the current field in square brackets where indicated and the required year in place of Year. This example will display all the records with a date in 1998 in the Invoice Date field.

<Date()-30
To match a particular calculated date range you will need to use a combination of expressions. This expression employs a calculation that subtracts 30 from the current date and also includes the less than operator. This example will display all the records with a date more than 30 days old in the Invoice Date field.