Access date validation rule examples
Validation rules for dates have a lot in common with validation rules for numbers. Just as with numbers, writing validation rules for dates is a matter of testing if a data is greater or smaller than a certain date, or testing if a date is withing a certain range.
How to write dates
Dates in Access are written between number signs (#). If you don't ad number signs Access 2010 will add them automatically when you enter a date into the Validation Rule field. The following validation rule example validates dates after 2/28/2014. Note that these validation rules use the American date format (mm/dd/yyyy).
> #2/28/2014# (validates dates after 2/28/2014)
Date/Time values can also include a time component.
> #2/28/2014 22:15:54#
If you omit the time component, Access will assume the time 00:00:00 automatically. So, writing #2/28/2014# is the same as #2/28/2014 00:00:00#. That is why the DateTime value #2/28/2014# is considered earlier than #2/28/2014 3:15:25#.
In Access, you can use the date format that is common to your locale. In the United States this will usually be mm/dd/yyyy, but in Europe this might be dd/mm/yyyy.
You can use the following date functions in date validation rules.
|Date()||The current date|
|Now()||The current date and time|
Below are some examples of date validation rules. These examples use the European time format (dd/mm/yyyy). You can change these rule to match your locale.
|> #14/5/1999#||Validates dates after 14/5/1999|
|>= #14/5/1999#||Validates dates after and including 14/5/1999|
|> #14/5/1999# AND < #14/5/2015#||Validates dates between, but not inlcuing 14/5/1999 and 14/5/2015|
|> Date()||Validates dates after the current date|
|> Now()||Validates dates/times after the current date and time|
|= Date()||Validates today's date only|