Creating Queries  «Prev  Next»
Lesson 11 Using multiple criteria
Objective Use multiple criteria to limit the results of a query.

Using Multiple Criteria in Access

In some cases one criterion may not be enough to limit the data to only the records that you want to see. You may want to combine criteria.

Logical operators


When you use multiple criteria you must use an operator to tell Access how they work together. You do this by using Logical Operators.
View the Table below to examine a chart of the logical operators.

Logical Operators

OperatorHow it works
ANDTells Access that a record must meet more than one criteria to be shown in the datasheet
OR Tells Access that a record must meet only one of several criteria in order to be shown in the datasheet
NOT Tells Access that a criteria must be false in order for the record to be shown in the datasheet
AND is the more limiting operator--it tells Access that a record must meet both the criteria before the operator and the criteria after the operator in order to be shown on the datasheet. OR is a more lenient operator, it tells Access that as long as a record meets one of the criteria, it can be displayed on the datasheet.

Multiple criteria in a single row

When you use criteria for more than one field, and put them all in the Criteria row of the query grid, Access assumes that you mean to join them with AND. So a record must meet all criteria within that row in order to be displayed on the datasheet.
081 Row

Or rows in the query grid

If you have multiple criteria for one field and you want to join these criteria with OR, use the Or row(s) in the query grid (when you use the first Or row, additional Or rows appear).
Similarly, if you have criteria for multiple fields that you want to join with Or, put the first criteria in the Criteria row, and the next in the Or row. Each additional criteria should go in it’s own Or row.
Multiple Rows
Multiple Rows
When you use multiple criteria in a single Or row, those criteria, confusingly enough, are considered to be joined with AND (just as multiple criteria in the Criteria row are considered to be joined with AND).
Learn to refine your query by specifying a sort order.

Matching Access Criteria - Quiz

Test your understanding of logical operators with this quiz.
Matching Access Criteria - Quiz