MS Access - Query Criteria


Advertisements


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 limit 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 click on Query Design.

Query Design

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

Tblemployees

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

Query Grid

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

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.

Marketing Coordinator

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

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 −

Accounting Assistant

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

Run Query

If you need to use the functionality 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.

And Operator

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

Run Query

Advertisements
E-Books Store