Lesson 22: Query Criteria Quick Reference Guide

/en/access2013/creating-a-find-duplicates-query/content/

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 NameWrite it like...Function
Equals"x"Searches for values equal to x
Does Not EqualNot in ("x")Searches for all values except those equal to x
NullIs NullSearches for empty fields
Not NullIs Not NullSearches for non-empty fields

Simple criteria for text:

Criteria NameWrite it like...Function
ContainsLike "*x*"Searches for all values that contain x
Does Not ContainNot like "*x*"Searches for all values except those that contain x
Begins withLike "x*"Searches for all values beginning with x
Ends withLike "*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 NameWrite it like...Function
BetweenBetween "x" and "y"Searches for values in the range between x and y
Less Than< xSearches for all values smaller than x
Less Than or Equal To<= xSearches for all valuessmaller than or equal to x
Greater Than> xSearches for all values larger than x
Greater Than or Equal To>= xSearches for all values larger than or equal to x

Simple criteria for dates:

Criteria NameWrite it like...Function
BetweenBetween "#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()-xSearches for all records containing dates x or more days in the past

/en/access2013/4-free-alternatives-to-microsoft-office/content/