Limiting Records with Criteria Expressions

In addition to using queries to select only a few fields to show, you may also (even often) use queries to display a limited selection of records. Criteria enable you to limit the records that the query displays. You use the Criteria and Or rows in the design grid to tell Access exactly which records you want to see.

Querying by example

Querying by example - QBE, for short - makes defining criteria easy: If you tell Access what you're looking for, Access goes out and finds it. For example, if you want to find values equal to 10, the criterion is simply 10. Access then finds records that match - that are equal to 10.

The most common type of criterion is a logical expression. A logical expression gives a Yes or No answer. Access shows you the record if the answer is Yes, but does not show the record if the answer is No. The operators commonly used in logical expressions include <, >, AND, OR, and NOT.

Although we use uppercase to distinguish operators and functions, case does not matter in the design grid.

If you want to find all the addresses in New York, the criterion for the State field is simply the following:

You may want to add another criterion in the next line (OR) to take care of different spellings, as follows:

Access puts the text in quotes for you. The result of the query is all records that have either NY or New York in the State field.

You can find records with null values by using the Is Null criterion. If you want all records except those with null values, use the Is Not Null criterion.