Course summary: Using date criteria in queries

The basics

Surround your date values with pound signs (#). For example, #4/1/1997#. Also, keep logical operators outside pound signs.

• #4/1/1997# Or #5/4/1997#

• >=#5/4/1997# Or <=#6/12/1998#

Filter for a given year

=Year([Acquired Date]), then put the year you want to filter for in the Criteria row.

Filter for an interval (year, quarter, etc.)

=DatePart("q",[acquired date]). In this example, put the numbers 1-4 (the quarter you want to filter for) in the Criteria row.

Subtract dates

=DateDiff("d",[RequiredDate],[ShippedDate]). Finds the days between a required date and a shipped date.

Add dates

=DateAdd("m",6,"4/1/1997") Finds 6 months of data, starting with the date provided.

DatePart, DateDiff, & DateAdd interval settings

See also

• Using dates as criteria in Access queries

• Build an expression

• Choose the right date function

• DatePart, DateDiff, & DateAdd

• More training courses