Thursday, January 21, 2021

Apply criteria to text values

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.

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 search

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.

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 criteria

Is Null

Displays last names of contacts with a null value in the Country/Region column.

image of query designer with the is not criteria

Is 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 blank

Not ""

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

No comments:

Post a Comment