DBWebApplications 





Refining Access Queries  «Prev  Next»
Lesson 4 Working with multiple query criteria
Objective Use multiple criteria in a query using the AND and OR operators.

Working with Multiple Query Criteria

You saw in the last lesson how to specify parameters to make your query more flexible. Now it’s time to see how to make your queries more powerful by using multiple criteria. Access gives you the ability to do this by using the AND and OR operators. When you use multiple criteria, you are said to be using complex criteria.

The AND operator

The AND operator tells Access that both criteria have to be true in order for the record to be displayed or used. When using the AND operator in the Query Design view, you will be setting the criteria row in the fields used across the criteria grid. Here is an example using the Clients table in the project database, where LastName starts with C and the City field is Phoenix:
AND operator results
AND operator results
The table below shows another way to look at how the AND operator is evaluated, where there are two criteria being evaluated by the AND operator.
Criteria 1 = True Criteria 1 = False
Criteria 2 = True Result = True Result = False
Criteria 2 = False Result = False Result = False


The OR operator

The OR operator tells Access that either one thing or another can be true for the record to be displayed or used. The OR operator is specified by putting the criteria downward from the criteria grid in the Query Design view. Here is an example that uses the Clients table in the project database, where LastName starts with C OR City is Phoenix. Here is what the query would look like in Design view:

OR operator results
OR operator results
The table below shows another way to look at how the ORoperator is evaluated, where there are two criteria being evaluated by the OR operator.

Using OR Criteria 1 = True Criteria 1 = False
Criteria 2 = True Result = True Result = True
Criteria 2 = False Result = True Result = False

Notice that either of the criteria can be True to have the result be True. You can combine AND and OR statements in your queries to handle complex problems.
In the next lesson, you will learn how to set properties for fields in queries.

Filtering multiple Criteria - Quiz

Click the Quiz link below to answer a few questions about filtering query results, parameter queries, and multiple criteria.
Filtering multiple Criteria - Quiz

Multiple Query Criteria - Exercise

Click the Exercise link below to practice creating a query using an AND clause.
Multiple Query Criteria - Exercise