Examples of query criteria

Access for Microsoft 365 Access 2021 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007
More...Less

Query criteria help you zero in on specific items in an Access database. If an item matches all the criteria
you enter, it appears in the query results.

To add criteria to an Access query, open the query in Design view and identify the fields (columns) you
want to specify criteria for. If the field is not in the design grid, double-click the field to add it to the
design grid and then enter the criterion in the Criteria row for that field. If you’re not sure how to make
this happen, see Introduction to queries.

A query criterion is an expression that Access compares to query field values to determine whether to
include the record that contains each value. For example, = "Chicago" is an expression that Access can
compare to values in a text field in a query. If the value for that field in a given record is "Chicago",
Access includes the record in the query results.

Here are some examples of commonly used criteria you can use as a starting point to create your
criteria. The examples are grouped by data types.

In this topic

 Introduction to query criteria

 Criteria for Text, Memo, and Hyperlink fields

 Criteria for Number, Currency, and AutoNumber fields

 Criteria for Date/Time fields

 Criteria for Yes/No fields

 Criteria for other fields

Introduction to query criteria

A criterion is similar to a formula — it is a string that may consist of field references, operators, and
constants. Query criteria are also referred to as expressions in Access.

The following tables shows some sample criteria and explains how they work.
Criteria Description

This criterion applies to a Number field, such as Price or UnitsInStock. It includes

>25 and <50 only those records where the Price or UnitsInStock field contains a value greater
than 25 and less than 50.

DateDiff ("yyyy", This criterion applies to a Date/Time field, such as BirthDate. Only records
[BirthDate], Date()) > where the number of years between a person's birthdate and today's date is
30 greater than 30 are included in the query result.

This criterion can be applied to any type of field to show records where the field
Is Null
value is null.

As you can see, criteria can look very different from each other, depending on the data type of the field
to which they apply and your specific requirements. Some criteria are simple, and use basic operators
and constants. Others are complex, and use functions, special operators, and include field references.

This topic lists several commonly used criteria by data type. If the examples given in this topic do not
address your specific needs, you might need to write your own criteria. To do that, you must first
familiarize yourself with the full list of functions, operators, special characters, and the syntax for
expressions referring to fields and literals.

Here, you will see where and how you add the criteria. To add a criteria to a query, you must open the
query in Design view. You then identify the fields for which you want to specify criteria. If the field is not
already in the design grid, you add it by either dragging it from the query design window to the field
grid, or by double-clicking the field (Double-clicking the field automatically adds it to the next empty
column in the field grid.). Finally, you type the criteria in the Criteria row

Criteria that you specify for different fields in the Criteria row are combined by using the AND operator.
In other words, the criteria specified in the City and BirthDate fields are interpreted like this:

City = "Chicago" AND BirthDate < DateAdd (" yyyy ", -40, Date())

1. The City and BirthDate fields include criteria.

2. Only records where the value of the City field is Chicago will satisfy this criterion.

3. Only records of those who are at least 40 years old will satisfy this criterion.

4. Only records that meet both criteria will be included in the result.

What if you want only one of these conditions to be met? In other words, if you have alternate criteria,
how do you enter them?
If you have alternate criteria, or two sets of independent criteria where it is sufficient to satisfy one set,
you use both the Criteria and the or rows in the design grid.

1. The City criterion is specified in the Criteria row.

2. The BirthDate criterion is specified in the or row.

Criteria specified in the Criteria and or rows are combined using the OR operator, as shown below:

City = "Chicago" OR BirthDate < DateAdd (" yyyy ", -40, Date())

If you need to specify more alternatives, use the rows below the or row.

Before you continue with the examples, note the following:

 If the criteria is temporary or changes often, you can filter the query result instead of frequently
modifying the query criteria. A filter is a temporary criterion that changes the query result
without altering the design of the query. For more information about filters, see the article
Apply a filter to view select records in an Access database.

 If the criteria fields don't change, but the values you are interested in do change frequently, you
can create a parameter query. A parameter query prompts the user for field values, and then
uses those values to create the query criteria. For more information about parameter queries,
see the article Use parameters in queries and reports.

Criteria for Text, Memo, and Hyperlink fields

Note: Beginning in Access 2013, Text fields are now named Short Text and Memo fields are now named
Long Text.

The following examples are for the CountryRegion field in a query that is based on a table that stores
contacts information. The criterion is specified in the Criteria row of the field in the design grid.

A criterion that you specify for a Hyperlink field is, by default, applied to the display text portion of the
field value. To specify criteria for the destination Uniform Resource Locator (URL) portion of the value,
use the HyperlinkPart expression. The syntax for this expression is as follows:
HyperlinkPart([Table1].[Field1],1) = "http://www.microsoft.com/", where Table1 is the name of the
table containing the hyperlink field, Field1 is the hyperlink field, and http://www.microsoft.com is the
URL you want to match.
To include records
Use this criterion Query result
that...

Exactly match a
Returns records where the CountryRegion field is set to
value, such as "China"
China.
China

Do not match a
Returns records where the CountryRegion field is set to a
value, such as Not "Mexico"
country/region other than Mexico.
Mexico

Returns records for all countries/regions whose names

start with "U", such as UK, USA, and so on.
Begin with the
specified string, Like U* Note: When used in an expression, the asterisk (*)
such as U represents any string of characters — it is also called a
wildcard character. For a list of such characters, see the
article Access wildcard character reference.

Do not begin with

Returns records for all countries/regions whose names
the specified string, Not Like U*
start with a character other than "U".
such as U

Contain the
Returns records for all countries/regions that contain the
specified string, Like "*Korea*"
string "Korea".
such as Korea

Do not contain the

Returns records for all countries/regions that do not
specified string, Not Like "*Korea*"
contain the string "Korea".
such as Korea

End with the

Returns records for all countries/regions whose names
specified string, Like "*ina"
end in "ina", such as China and Argentina.
such as "ina"

Do not end with

Returns records for all countries/regions that do not end
the specified string, Not Like "*ina"
in "ina", such as China and Argentina.
such as "ina"

Contain null (or

Is Null Returns records where there is no value in the field.
missing) values
To include records
Use this criterion Query result
that...

Do not contain null Returns records where the value is not missing in the
Is Not Null
values field.

Returns records where the field is set to a blank (but not

Contain zero- null) value. For example, records of sales made to
"" (a pair of quotes)
length strings another department might contain a blank value in the
CountryRegion field.

Do not contain Returns records where the CountryRegion field has a

Not ""
zero-length strings nonblank value.

Contains null
Returns records where there is either no value in the
values or zero- "" Or Is Null
field, or the field is set to a blank value.
length strings

Is not empty or Returns records where the CountryRegion field has a

Is Not Null And Not ""
blank nonblank, non-null value.

Follow a value,
such as Mexico, Returns records of all countries/regions, beginning with
>= "Mexico"
when sorted in Mexico and continuing through the end of the alphabet.
alphabetical order

Fall within a
Returns records for countries/regions whose names start
specific range, such Like "[A-D]*"
with the letters "A" through "D".
as A through D

Match one of two

values, such as USA "USA" Or "UK" Returns records for USA and UK.
or UK

Contain one of the

In("France", "China", Returns records for all countries/regions specified in the
values in a list of
"Germany", "Japan") list.
values

Contain certain
characters at a Right([CountryRegion], Returns records for all countries/regions where the last
specific position in 1) = "y" letter is "y".
the field value
To include records
Use this criterion Query result
that...

Satisfy length Len([CountryRegion]) > Returns records for countries/regions whose name is
requirements 10 more than 10 characters long.

Returns records for countries/regions, such as China and

Chile, whose names are five characters long and the first
three characters are "Chi".

Note: The characters ? and _, when used in an

Match a specific expression, represent a single character — these are also
Like "Chi??"
pattern called wildcard characters. The character _ cannot be
used in the same expression with the ? character, nor
can it be used in an expression with the * wildcard
character. You may use the wildcard character _ in an
expression that also contains the % wildcard character.

Criteria for Number, Currency, and AutoNumber fields

The following examples are for the UnitPrice field in a query that is based on a table that stores products
information. The criterion is specified in the Criteria row of the field in the query design grid.

To include Use this

Query Result
records that... criterion

Exactly match a
value, such as 100 Returns records where the unit price of the product is $100.
100

Do not match a
value, such as Not 1000 Returns records where the unit price of the product is not $1000.
1000

Contain a value
Returns records where the unit price is less than $100 (<100). The second
smaller than a < 100
expression (<=100) displays records where the unit price is less than or
value, such as <= 100
equal to $100.
100
To include Use this
Query Result
records that... criterion

Contain a value
Returns records where the unit price is greater than $99.99 (>99.99). The
larger than a >99.99
second expression displays records where the unit price is greater than or
value, such as >=99.99
equal to $99.99.
99.99

Contain one of
the two values, 20 or 25 Returns records where the unit price is either $20 or $25.
such as 20 or 25

>49.99
and
Contain a value <99.99
Returns records where the unit price is between (but not including) $49.99
that falls with a -or-
and $99.99.
range of values Between
50 and
100

Contain a value
<50 or
that falls Returns records where the unit price is not between $50 and $100.
>100
outside a range

Contain one of
In(20, 25,
many specific Returns records where the unit price is either $20, $25, or $30.
30)
values

Returns records where the unit price ends with "4.99", such as $4.99,
$14.99, $24.99, and so on.
Contain a value Note: The characters * and %, when used in an expression, represent any
that ends with Like number of characters — these are also called wildcard characters. The
the specified "*4.99" character % cannot be used in the same expression with the * character,
digits nor can it be used in an expression with the ? wildcard character. You may
use the wildcard character % in an expression that also contains the _
wildcard character.

Contain null null

(or missing) Is Null Returns records where no value is entered in the UnitPrice field.
values
To include Use this
Query Result
records that... criterion

Contain non-
Is Not Null Returns records where the value is not missing in the UnitPrice field.
null values

Criteria for Date/Time fields

The following examples are for the OrderDate field in a query based on a table that stores Orders
information. The criterion is specified in the Criteria row of the field in the query design grid.

To include records
Use this criterion Query result
that ...

Returns records of
transactions that took place
Exactly match a on Feb 2, 2006. Remember to
value, such as #2/2/2006# surround date values with the
2/2/2006 # character so that Access can
distinguish between date
values and text strings.

Returns records of
Do not match a
transactions that took place
value, such as Not #2/2/2006#
on a day other than Feb 2,
2/2/2006
2006.

Returns records of
transactions that took place
Contain values that before Feb 2, 2006.
fall before a
< #2/2/2006# To view transactions that took
certain date, such
as 2/2/2006 place on or before this date,
use the <= operator instead of
the < operator.

Contain values that

Returns records of
fall after a certain
> #2/2/2006# transactions that took place
date, such as
after Feb 2, 2006.
2/2/2006
To include records
Use this criterion Query result
that ...

To view transactions that took

place on or after this date, use
the >= operator instead of the
> operator.

Returns records where the

transactions took place
between Feb 2, 2006 and Feb
4, 2006.
Contain values that You can also use the Between
fall within a date >#2/2/2006# and <#2/4/2006# operator to filter for a range
range of values, including the end
points. For example, Between
#2/2/2006# and #2/4/2006#
is the same as >=#2/2/2006#
and <=#2/4/2006# .

Returns records where the

Contain values that transactions took place before
<#2/2/2006# or >#2/4/2006#
fall outside a range Feb 2, 2006 or after Feb 4,
2006.

Contain one of two Returns records of

values, such as transactions that took place
#2/2/2006# or #2/3/2006#
2/2/2006 or on either Feb 2, 2006 or Feb
2/3/2006 3, 2006.

Returns records where the

Contain one of transactions took place on
In (#2/1/2006#, #3/1/2006#, #4/1/2006#)
many values Feb 1, 2006, March 1, 2006, or
April 1, 2006.

Contain a date that

falls in a specific
Returns records where the
month
DatePart("m", [SalesDate]) = 12 transactions took place in
(irrespective of
December of any year.
year), such as
December
To include records
Use this criterion Query result
that ...

Contain a date that

falls in a specific
Returns records where the
quarter
DatePart("q", [SalesDate]) = 1 transactions took place in the
(irrespective of
first quarter of any year.
year), such as the
first quarter

Returns records of
transactions that took place
Contain today's on the current day. If today's
Date()
date date is 2/2/2006, you see
records where the OrderDate
field is set to Feb 2, 2006.

Returns records of
transactions that took place
Contain the day before the current
Date()-1
yesterday's date day. If today's date is
2/2/2006, you see records for
Feb 1, 2006.

Returns records of
transactions that took place
Contain the day after the current day.
Date() + 1
tomorrow's date If today's date is 2/2/2006,
you see records for Feb 3,
2006.

Returns records of
Contain dates that transactions that took place
DatePart("ww", [SalesDate]) = DatePart("ww",
fall during the during the current week. A
Date()) and Year( [SalesDate]) = Year(Date())
current week week starts on Sunday and
ends on Saturday.

Returns records of
Contain dates that transactions that took place
Year([SalesDate])* 53 + DatePart("ww", [SalesDate])
fell during the during the last week. A week
= Year(Date())* 53 + DatePart("ww", Date()) - 1
previous week starts on Sunday and ends on
Saturday.
To include records
Use this criterion Query result
that ...

Returns records of
Contain dates that transactions that will take
Year([SalesDate])* 53+DatePart("ww", [SalesDate])
fall during the place next week. A week
= Year(Date())* 53+DatePart("ww", Date()) + 1
following week starts on Sunday and ends on
Saturday.

Returns records of
transactions that took place
Contain a date that
during the last 7 days. If
fell during the last Between Date() and Date()-6
today's date is 2/2/2006, you
7 days
see records for the period Jan
24, 2006 through Feb 2, 2006.

Returns records for the

Contain a date that
Year([SalesDate]) = Year(Now()) And current month. If today's date
belongs to the
Month([SalesDate]) = Month(Now()) is 2/2/2006, you see records
current month
for Feb 2006.

Returns records for the

Contain a date that
Year([SalesDate])* 12 + DatePart("m", [SalesDate]) previous month. If today's
belongs to the
= Year(Date())* 12 + DatePart("m", Date()) - 1 date is 2/2/2006, you see
previous month
records for Jan 2006.

Returns records for the next

Contain a date that
Year([SalesDate])* 12 + DatePart("m", [SalesDate]) month. If today's date is
belongs to the next
= Year(Date())* 12 + DatePart("m", Date()) + 1 2/2/2006, you see records for
month
Mar 2006.

A month's worth of sales

Contain a date that records. If today's date is
fell during the last Between Date( ) And DateAdd("M", -1, Date( )) 2/2/2006, you see records for
30 or 31 days the period Jan 2, 2006. to Feb
2, 2006

Returns records for the

Contain a date that current quarter. If today's
Year([SalesDate]) = Year(Now()) And DatePart("q",
belongs to the date is 2/2/2006, you see
Date()) = DatePart("q", Now())
current quarter records for the first quarter of
2006.
To include records
Use this criterion Query result
that ...

Returns records for the

Contain a date that previous quarter. If today's
Year([SalesDate])*4+DatePart("q",[SalesDate]) =
belongs to the date is 2/2/2006, you see
Year(Date())*4+DatePart("q",Date())- 1
previous quarter records for the last quarter of
2005.

Returns records for the next

Contain a date that
Year([SalesDate])*4+DatePart("q",[SalesDate]) = quarter. If today's date is
belongs to the next
Year(Date())*4+DatePart("q",Date())+1 2/2/2006, you see records for
quarter
the second quarter of 2006.

Returns records for the

Contain a date that
current year. If today's date is
falls during the Year([SalesDate]) = Year(Date())
2/2/2006, you see records for
current year
the year 2006.

Returns records of
Contain a date that transactions that took place
belongs to the Year([SalesDate]) = Year(Date()) - 1 during the previous year. If
previous year today's date is 2/2/2006, you
see records for the year 2005.

Returns records of
Contain a date that transactions with next year's
belongs to next Year([SalesDate]) = Year(Date()) + 1 date. If today's date is
year 2/2/2006, you see records for
the year 2007.

Returns records of
transactions with dates that
Contain a date that
Year([SalesDate]) = Year(Date()) and fall between Jan 1 of the
falls between Jan 1
Month([SalesDate]) <= Month(Date()) and current year and today. If
and today (year to
Day([SalesDate]) <= Day (Date()) today's date is 2/2/2006, you
date records)
see records for the period Jan
1, 2006 to to 2/2/2006.

Contain a date that Returns records of

occurred in the < Date() transactions that took place
past before today.
To include records
Use this criterion Query result
that ...

Contain a date that Returns records of

occurrs in the > Date() transactions that will take
future place after today.

Filter for null (or Returns records where the

Is Null
missing) values date of transaction is missing.

Filter for non-null Returns records where the

Is Not Null
values date of transaction is known.

Criteria for Yes/No fields

As an example, your Customers table has a Yes/No field named Active, used to indicate whether a
customer's account is currently active. The following table shows how values entered in the Criteria row
for a Yes/No field are evaluated.

Field value Result

Tested for a Yes value. A value of 1 or -1 is converted to "True" in

Yes, True, 1, or -1
the Criteria row after you enter it.

Tested for a No value. A value of 0 is converted to "False" in the

No, False, or 0
Criteria row after you enter it.

No value (null) Not tested

Any number other than 1, -1, or 0 No results if it's the only criteria value in the field

Any character string other than Yes,

Query fails to run due to Data type mismatch error
No, True, or False

Criteria for other fields

Attachments In the Criteria row, type Is Null to include records that do not contain any attachments.
Type Is Not Null to include records that contain attachments.

Lookup fields There are two types of Lookup fields: those that look up values in an existing data source
(by using a foreign key), and those that are based on a list of values specified when the Lookup field is
created.

Lookup fields that are based on a list of specified values are of the Text data type, and valid criteria are
the same as for other text fields.
The criteria you can use in a Lookup field based on values from an existing datasource depend on the
data type of the foreign key, rather than the data type of the data being looked up. For example, you
may have a Lookup field that displays Employee Name, but uses a foreign key that is of the Number data
type. Because the field stores a number instead of text, you use criteria that work for numbers; that is,
>2.

If you do not know the data type of the foreign key, you can inspect the source table in Design view to
determine the data types of the field. To do this:

1. Locate the source table in the Navigation Pane.

2. Open the table in Design view by either:

o Clicking the table, and then pressing CTRL+ENTER

o Right-clicking the table, and then clicking Design View.

3. The data type for each field is listed in the Data Type column of the table design grid.

Multivalued fields Data in a multivalued field are stored as rows in a hidden table that Access creates
and populates to represent the field. In query Design view, this is represented in the Field List by using
an expandable field. To use criteria for a multivalued field, you supply criteria for a single row of the
hidden table. To do this:

1. Create a query containing the multivalued field, and open it in Design view.

2. Expand the multivalued field by clicking the plus symbol (+) next to it — if the field is already
expanded, this is a minus symbol (-). Just below the name of the field, you will see a field
representing a single value of the multivalued field. This field will have the same name as the
multivalued field, with the string .Value appended.

3. Drag the multivalued field and its single value field to separate columns in the design grid. If you
want to see only the complete multivalue field in your results, clear the Show check box for the
single value field.

4. Type your criteria in the Criteria row for the single value field, using criteria that is appropriate
for whatever type of data the values represent.

5. Each value in the multivalued field will be individually evaluated using the criteria you supply.
For example, you may have a multivalued field that stores a list of numbers. If you supply the
criteria >5 AND <3, any record where there is at least one value greater than 5 and one value
less than 3 will match.