Examples of Access query criteria

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.

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 article

Examples of criteria for Text fields

Let’s say we have a contacts information table. The examples below show various criteria for the CountryRegion field in that query. The highlighted Criteria row is where you add the criterion.

CountryRegion criteria

To include Items that...

Use this criterion

Query result returns items…

Exactly match a value, such as China

"China"

Where the CountryRegion field is set to China.

For more see, applying criteria to text values.

Do not match a value, such as Mexico

Not "Mexico"

Where the CountryRegion field is set to a country/region other than Mexico.

Begin with the specified string, such as U

Like U*

For all countries/regions whose names start with "U", such as UK, or USA.

Note:  When used in an expression, the asterisk (*) represents any string of characters — it's also called a wildcard character. For a list of such characters, see the article Access wildcard character reference.

Do not begin with the specified string, such as U

Not Like U*

For all countries/regions whose names start with a character other than "U".

Contain the specified string, such as Korea

Like "*Korea*"

For all countries/regions that contain the string "Korea".

Doesn’t have the specified string, such as Korea

Not Like "*Korea*"

For all countries/regions that do not contain the string "Korea".

End with the specified string, such as "ina"

Like "*ina"

For all countries/regions whose names end in "ina", such as China and Argentina.

Do not end with the specified string, such as "ina"

Not Like "*ina"

For all countries/regions that do not end in "ina", such as China and Argentina.

Contain null (or missing) values

Is Null

Where there is no value in the field.

Do not contain null values

Is Not Null

Where the value is not missing in the field.

Contain zero-length strings

"" (a pair of quotes)

Where the field is set to a blank (but not null) value. For example, items of sales made to another department might contain a blank value in the CountryRegion field.

Do not contain zero-length strings

Not ""

Where the CountryRegion field has a nonblank value.

Contains null values or zero-length strings

"" Or Is Null

Where there is either no value in the field, or the field is set to a blank value.

Is not empty or blank

Is Not Null And Not ""

Where the CountryRegion field has a nonblank, non-null value.

Follow a value, such as Mexico, when sorted in alphabetical order

>= "Mexico"

Of all countries/regions, beginning with Mexico and continuing through the end of the alphabet.

Fall within a specific range, such as A through D

Like "[A-D]*"

For countries/regions whose names start with the letters "A" through "D".

Match one of two values, such as USA or UK

"USA" Or "UK"

For USA and UK.

Contain one of the values in a list of values

In("France", "China", "Germany", "Japan")

For all countries/regions specified in the list.

Contain certain characters at a specific position in the field value

Right([CountryRegion], 1) = "y"

For all countries/regions where the last letter is "y".

Satisfy length requirements

Len([CountryRegion]) > 10

For countries/regions whose name is more than 10 characters long.

Match a specific pattern

Like "Chi??"

For countries/regions, such as China and Chile, whose names are five characters long and the first three characters are "Chi".

Note:  When used in an expression, characters ? and _, represent a single character. The wildcard character _ cannot be used in the same expression with the ? character, nor can it be used with the asterisk character.

See more information on applying criteria to text values.

Top of Page

Examples of criteria for Number and Currency fields

For this example, we have a UnitPrice query of a products table. Add the criteria to the criteria row and also the highlighted row if you have any OR criteria.

UnitPrice criteria

To include items that...

Use this criterion

Query result returns items (records) where the unit price…

Exactly match a value, such as 100

100

Of the product is $100.

Do not match a value, such as 1000

Not 1000

Of the product is not $1000.

Contain a value smaller than a value, such as 100

< 100
<= 100

Is less than $100 (<100). The second expression (<=100) displays Items where the unit price is less than or equal to $100.

Contain a value larger than a value, such as 99.99

>99.99
>=99.99

Greater than $99.99 (>99.99). The second expression displays Items where the unit price is greater than or equal to $99.99.

Contain one of the two values, such as 20 or 25

20 or 25

Is either $20 or $25.

Contain a value that falls with a range of values

>49.99 and <99.99
-or-
Between 50 and 100

Is between (but not including) $49.99 and $99.99.

Contain a value that falls outside a range

<50 or >100

Is not between $50 and $100.

Contain one of many specific values

In(20, 25, 30)

Is $20, $25, or $30.

Contain a value that ends with the specified digits

Like "*4.99"

Ends with "4.99", such as $4.99, $14.99, $24.99, and so on.

Contain null (or missing) values

Is Null

Items where no value is entered in the UnitPrice field.

Contain non-null values

Is Not Null

Items where the value is not missing in the UnitPrice field.

Examples of criteria for a Yes/No field

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

Yes, True, 1, or -1

Tested for a Yes value. A value of 1 or -1 is converted to "True" in the Criteria row after you enter it.

No, False, or 0

Tested for a No value. A value of 0 is converted to "False" in the 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, No, True, or False

Query fails to run due to Data type mismatch error

Date/Time criteria

There are many ways to use Date/Time criteria in a query. See the article Using dates as criteria in Access queries.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×