You can use query criteria in Access to limit the results based on specific text values. For example the criterion, = "Chicago" shows all items that have the text Chicago. This article has several examples of query criteria that you can use with the Text data type that can help you get more specific query results and find the information that you want more quickly. If you’re not sure about using criteria see, applying criteria to a query.
Sample Text criteria and results
The table below shows how you can use some of the common criteria for Text data types. Try using the different criteria and see what results you get. If you happen to see no items in the results, you can recheck your criteria but it might just mean that there weren’t any items that exactly matched your criteria.
Note: Access automatically adds the quote marks at the end of each criterion but you can add the quotes when you use text that might confuse the query. For example, if you use a phrase that has the words "and" or "or". Access interprets them as instructions.
|
For this result |
Query Designer view |
Criteria |
|
To find all items that matches the text exactly. Displays only contacts in the US. |
|
"Text" |
|
The OR criteria row finds matches to multiple words or phrases. Displays contacts in USA, China or Canada. |
|
"Text" |
|
To exclude text, use the "Not" criteria followed by the word or phrase you want to exclude. Displays contacts in all the cities except Boise. |
|
Not "Text" |
|
To exclude text by multiple conditions Displays all contacts that are not in Boise or New York or Las Vegas. Tip: Not Like "X*" finds all items except those starting with the specified letter. |
|
Not In ("Text", "Text", "Text"…) |
|
You can use the NOT criterion with other expressions like,AND NOT followed by the text that you want to exclude from your search. Displays all contacts from cities starting with the letter “L” except contacts in London. |
|
Like “Text*” And Not "Text" |
|
To search by last three letters of text. Displays any contact whose country or region name end in "ina", like China and Argentina. |
|
Like "*ina" |
|
Displays last name of contacts where the country/region has no text. |
|
Is Null |
|
Displays last names of contacts with a null value in the Country/Region column. |
|
Is Not Null |
|
Displays last names of contacts whose city name is blank (but not null). |
|
"" (a pair of quotes) |
|
Displays last names of contacts that have information in the city column. |
|
Not "" |
|
Displays contacts where the city information is neither blank nor null value. |
|
Is Not Null And Not "" |
Tips:
-
If you’d like to check out the query syntax in SQL (Structured Query Language), click the SQL View on the toolbar at the bottom right of your screen.
-
