Access for Microsoft 365 Access 2019 Access 2016 Access 2013 Access 2010 Access 2007
You can use query criteria in Access to restrict the results associated with particular text values. For instance, the criterion, = “Chicago” displays every item that contain the text Chicago. This article includes many possibilities of query criteria that you can interweave with the Text data type that can assist you with acquiring additional distinct query results and capture your desired data more rapidly. If you’re unsure about making use of criteria see, applying criteria to a query.
Sample Text criteria and results
The table beneath indicates how you can apply some of the typical criteria for Text data types. Experiment with applying the multiple criteria and check your results. If you find yourself discovering no records in the results, you can review your criteria but it may simply reveal that there were no items that precisely reflected your criteria.
Note: Access instantly creates the quote marks at the end of each criterion but you can append the quotes anytime you use text that could cause trouble for the query. For example, if you employ a phrase that includes the words “and” or “or”. Access translates them as commands.
|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.
|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.
|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.
|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 “”|
- 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.