WHERE Clause

Specifies which records from the tables listed in the FROM clause are affected by a SELECT, UPDATE, or DELETE statement.


SELECT fieldlist
FROM tableexpression
WHERE criteria

A SELECT statement containing a WHERE clause has these parts:

Part Description
fieldlist The name of the field or fields to be retrieved along with any field-name aliases (alias (SQL): An alternative name for a table or field in expressions. Often used to shorten the table or field name for subsequent references in code, to prevent possible ambiguous references, or to provide a more descriptive name in query output.), selection predicates (ALL, DISTINCT, DISTINCTROW, or TOP), or other SELECT statement options.
tableexpression The name of the table or tables from which data is retrieved.
criteria An 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.) that records must satisfy to be included in the query results.

The Microsoft Access database engine selects the records that meet the conditions listed in the WHERE clause. If you do not specify a WHERE clause, your query returns all rows from the table. If you specify more than one table in your query and you have not included a WHERE clause or a JOIN clause, your query generates a Cartesian product (Cartesian product: The result of executing an SQL SELECT statement that includes two or more tables in the FROM clause, but no WHERE or JOIN clause that indicates how the tables are to be joined.) of the tables.

WHERE is optional, but when included, follows FROM. For example, you can select all employees in the sales department (WHERE Dept = 'Sales') or all customers between the ages of 18 and 30 (WHERE Age Between 18 And 30).

If you do not use a JOIN clause to perform SQL join operations on multiple tables, the resulting Recordset object will not be updatable.

WHERE is similar to HAVING. WHERE determines which records are selected. Similarly, once records are grouped with GROUP BY, HAVING determines which records are displayed.

Use the WHERE clause to eliminate records you do not want grouped by a GROUP BY clause.

Use various expressions to determine which records the SQL statement returns. For example, the following SQL statement selects all employees whose salaries are more than $21,000:

SELECT LastName, Salary FROM Employees WHERE Salary > 21000;

A WHERE clause can contain up to 40 expressions linked by logical operators, such as And and Or.

When you enter a field name that contains a space or punctuation, surround the name with brackets ([ ]). For example, a customer information table might include information about specific customers :

SELECT [Customer’s Favorite Restarant]

When you specify the criteria argument, date literals (date literal: Any sequence of characters with a valid format that is surrounded by number signs (#). Valid formats include the date format specified by the locale settings for your code or the universal date format.) must be in U.S. format, even if you are not using the U.S. version of the Microsoft Access database engine. For example, May 10, 1996, is written 10/5/96 in the United Kingdom and 5/10/96 in the United States. Be sure to enclose your date literals with the number sign (#) as shown in the following examples.

To find records dated May 10, 1996 in a United Kingdom database, you must use the following SQL statement:

SELECT * FROM Orders WHERE ShippedDate = #5/10/96#;

You can also use the DateValue function which is aware of the international settings established by Microsoft Windows®. For example, use this code for the United States:

SELECT * FROM Orders WHERE ShippedDate = DateValue('5/10/96');

And use this code for the United Kingdom:

SELECT * FROM Orders WHERE ShippedDate = DateValue('10/5/96');

 Note   If the column referenced in the criteria string is of type GUID (GUID: A 16-byte field used in an Access database to establish a unique identifier for replication. GUIDs are used to identify replicas, replica sets, tables, records, and other objects. In an Access database, GUIDs are referred to as Replication IDs.) , the criteria expression uses a slightly different syntax:

WHERE ReplicaID = {GUID {12345678-90AB-CDEF-1234-567890ABCDEF}}

Be sure to include the nested braces and hyphens as shown.

Applies to:
Access 2007