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

Setting

Description

yyyy

Year

q

Quarter

m

Month

y

Day of year

d

Day

w

Weekday

ww

Week

h

Hour

n

Minute

s

Seconds

See also

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×