Creating Queries  «Prev  Next»
Lesson 9 Adding criteria to queries
Objective Describe how to use criteria to limit the results of a query.

Adding Criteria to Queries

Often what you want to do with a query is not just display data from different tables in one place, but somehow filter that data so that you only see data that meets certain criteria. Queries are the tool in Access designed to do that for you and you can use these queries later to create a form or report that shows only a subset of the data in your database.
The Criteria row in the query grid is where you define criteria. A criterion must be defined so that it applies to one field – you then put that criterion in the same column of the grid as the field it applies to. You can also have multiple criteria; that is, criteria for more than one field, or more than one criteria for one field .

Query by example

You began to learn about creating criterion when you learned about filtering tables. In query Design view you can also query by example; that is, you can simply type the value that you want Access to find. For instance, you could edit the Project Hours query to limit the records shown to those that include ABC Webworks:
ABC Webworks
ABC Webworks

Using operators

Often, though, your criteria will be more complicated than just finding a certain value in a certain field. When this is the case you need to use an operator.
View the Table below to see a table of the most commonly used relational operators.

Commonly Used Relational Operators

Operator What it means
=equal to
(this operator is usually understood and you do not need to type it)
<>
Not equal to
< Less than
<= Less than or equal to
> Greater than
>= Greater than or equal to
BETWEEN Between two values
i.e. BETWEEN 1 AND 5
NOT Not equal to

Learn about using values in character expressions in the next lesson.