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

Date format

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.

Some examples

You can use the following date functions in date validation rules.

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

Example Description
> #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