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.
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. A query criterion is similar to a formula — it is a string that may consist of field references, operators, and constants. Query criteria are a type of expression.
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.
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.
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.
