Query criteria quick reference guide
Below, you'll find a guide containing 20 of the most common criteria used in Access queries. While these criteria are all fairly simple, each one can help you carry out meaningful searches of your data. If you find this guide useful,
download the printable version
so you'll always have it handy. For a more comprehensive guide to criteria, look at Microsoft's official
Examples of query criteria
.
When entering the criteria, write them exactly as they are written in the second column, replacing
x
with your search term—or in the case of dates, replacing
mm/dd/yyyy
with the desired date.
Simple criteria for all data types:
|
Criteria Name
|
Write it like...
|
Function
|
|
Equals
|
"
x
"
|
Searches for values equal to
x
|
|
Does Not Equal
|
Not in ("
x
")
|
Searches for all values except those equal to
x
|
|
Null
|
Is Null
|
Searches for empty fields
|
|
Not Null
|
Is Not Null
|
Searches for non-empty fields
|
Simple criteria for text:
|
Criteria Name
|
Write it like...
|
Function
|
|
Contains
|
Like "*
x
*"
|
Searches for all values that contain
x
|
|
Does Not Contain
|
Not like "*
x
*"
|
Searches for all values except those that contain
x
|
|
Begins with
|
Like "
x
*"
|
Searches for all values beginning with
x
|
|
Ends with
|
Like "*
x
"
|
Searches for all values ending with
x
|
|
Comes After
|
>= "
x
"
|
Searches for all values that come after
x
in alphabetical order.
|
|
Comes Before
|
<= "
x
"
|
Searches for all values that come before
x
in alphabetical order.
|
Simple criteria for numbers:
|
Criteria Name
|
Write it like...
|
Function
|
|
Between
|
Between "
x
"
and "
y
"
|
Searches for values in the range between
x
and
y
|
|
Less Than
|
<
x
|
Searches for all values smaller than
x
|
|
Less Than or Equal To
|
<=
x
|
Searches for all valuessmaller than or equal to
x
|
|
Greater Than
|
>
x
|
Searches for all values larger than
x
|
|
Greater Than or Equal To
|
>=
x
|
Searches for all values larger than or equal to
x
|
Simple criteria for dates:
|
Criteria Name
|
Write it like...
|
Function
|
|
Between
|
Between "
#mm/dd/yy#
"
and "
#mm/dd/yy#
"
|
Searches for dates that fall between two dates.
|
|
Before
|
<
#mm/dd/yy#
|
Searches for dates before a certain date
|
|
After
|
>
#mm/dd/yy#
|
Searches for dates after a certain date.
|
|
Today
|
=Date()
|
Searches for all records containing today's date
|
|
Days Before Today
|
<=Date()-
x
|
Searches for all records containing dates
x
or more days in the past
|
