Access SQL: WHERE clause

This is one of a set of articles about Access SQL. This article describes how to write a WHERE clause, and uses examples to illustrate various techniques that you can use in a WHERE clause.

In a SQL statement, the WHERE clause specifies criteria that field values must meet for the records that contain the values to be included in the query results.

For an overview of Access SQL, see the article Access SQL: basic concepts, vocabulary, and syntax. For more information about other parts of Access SQL, see the See Also section.

In this article


Limit results by using criteria

When you want to use data to limit the number of records that are returned in a query, you can use criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.). A query criterion is similar to a formula — it is a string that may consist of field references, operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.), and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.). Query criteria are a type of expression (expression: Any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Expressions can perform calculations, manipulate characters, or test data.).

The following table shows some sample criteria and explains how they work.

Criteria Description
>25 and <50 This criterion applies to a Number field, such as Price or UnitsInStock. It includes only those records where the Price or UnitsInStock field contains a value greater than 25 and less than 50.
DateDiff ("yyyy", [BirthDate], Date()) > 30 This criterion applies to a Date/Time field, such as BirthDate. Only records where the number of years between a person's birth date and today's date is greater than 30 are included in the query result.
Is Null This criterion can be applied to any kind of field to show records where the field value is null.

As the previous table illustrates, criteria can look very different from each other depending on the data type of the field to which the criteria apply and your specific requirements. Some criteria are simple, and use basic operators and constants. Others are complex, and use functions and special operators, and include field references.

 Important   If a field is used with an aggregate function, you cannot specify criteria for that field in a WHERE clause. Instead, you use a HAVING clause to specify criteria for aggregated fields. For more information, see the articles Access SQL: basic concepts, vocabulary, and syntax and HAVING Clause.

Top of Page Top of Page

WHERE clause syntax

You use query criteria in the WHERE clause of a SELECT statement.

A WHERE clause has the following basic syntax:

WHERE field = criterion

For example, suppose that you want the telephone number of a customer, but you only remember that the customer's last name is Bagel. Instead of looking at all the telephone numbers in your database, you could use a WHERE clause to limit the results and make it easier to find the telephone number that you want. Assuming that last names are stored in a field that is named LastName, your WHERE clause appears as follows:

WHERE [LastName]='Bagel'

 Note   You do not have to base the criteria in your WHERE clause on the equivalence of values. You can use other comparison operators, such as greater than (>) or less than (<). For example, WHERE [Price]>100.

Top of Page Top of Page

Use the WHERE clause to combine data sources

Sometimes you may want to combine data sources based on fields that have matching data, but have different data types. For example, a field in one table may have a Number data type, and you want to compare that field to a field in another table that has a Text data type.

You cannot create a join between fields that have different data types. To combine data from two data sources based on values in fields that have different data types, you create a WHERE clause that uses one field as a criterion for the other field, by using the LIKE keyword.

For example, suppose that you want to use data from table1 and table2, but only when the data in field1 (a text field in table1) matches the data in field2 (a number field in table2). Your WHERE clause would resemble the following:

WHERE field1 LIKE field2

For more information about how to create criteria to use in a WHERE clause, see the article Examples of query criteria.

Top of Page Top of Page

 
 
Applies to:
Access 2007