Apply criteria to text values

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 resultQuery Designer viewCriteria
To find all items that matches the text exactly.Displays only contacts in the US.query criteria to display specific word results“Text”
The OR criteria row finds matches to multiple words or phrases.

Displays contacts in USA, China or Canada.
or criteria to match multiple words or phrases“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.
To exclude a word or phrase, use the "Not" criteria followed by the word or phrase you want to exclude.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.
To display all contacts not in UK or USA or France, use criteria Not In ("Text", "Text", "Text"…)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.
Image of query design using NOT with AND NOT followed by the text to be excluded from searchLike “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.
Query designer image showing criteria using folloeing operators, "like wildcard in a"Like “*ina”
Displays last name of contacts where the country/region has no text.Images shows ctriteria field in query designer with is null criteriaIs Null
Displays last names of contacts with a null value in the Country/Region column.image of query designer with the is not criteriaIs Not Null
Displays last names of contacts whose city name is blank (but not null).query designer with criteria set to show records with blank value field“” (a pair of quotes)
Displays last names of contacts that have information in the city column.query designer criteria set to country field not blankNot “”
Displays contacts where the city information is neither blank nor null value.query designer with criteria where City field is neither set to null nor is blank.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.
  • SQL view on the toolbar

Top of Page

Leave a Reply

%d bloggers like this: