MS Access - Query Criteria

Query criteria helps you to retrieve specific items from an Access database. If an item matches with all the criteria you enter, it appears in the query results. When you want to pmit the results of a query based on the values in a field, you use query criteria.

A query criterion is an expression that Access compares to query field values to determine whether to include the record that contains each value.

Some criteria are simple, and use basic operators and constants. Others are complex, and use functions, special operators, and include field references.

To add some criteria to a query, you must open the query in the Design View.

You then identify the fields for which you want to specify criteria.

Example

Let’s look at a simple example in which we will use criteria in a query. First open your Access database and then go to the Create tab and cpck on Query Design.

In the Tables tab on Show Table dialog, double-cpck on the tblEmployees table and then close the dialog box.

Let us now add some field to the query grid such as EmployeeID, FirstName, LastName, JobTitle and Email as shown in the following screenshot.

Let us now run your query and you will see only these fields as query result.

If you want to see only those whose JobTitle are Marketing Coordinator then you will need to add the criteria for that. Let’s go to the Query Design again and in Criteria row of JobTitle enter Marketing Coordinator.

Let us now run your query again and you will see that only Job title of Marketing Coordinators are retrieved.

If you want to add criteria for multiple fields, just add the criteria in multiple fields. Let us say we want to retrieve data only for “Marketing Coordinator” and “Accounting Assistant”; we can specify the OR row operator as shown in the following screenshot −

Let us now run your query again and you will see the following results.

If you need to use the functionapty of the AND operator, then you have to specify the other condition in the Criteria row. Let us say we want to retrieve all Accounting Assistants but only those Marketing Coordinator titles with “Pollard” as last name.

Let us now run your query again and you will see the following results.