>
WHERE Clause
Description
Specifies which records from the tables listed in the FROM clause are affected by a SELECT, UPDATE, or DELETE statement.
Syntax
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, 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 that records must satisfy to be included in the query results. |
Remarks
The Microsoft Jet database engine selects the records that meet the conditions listed in the WHERE clause. If you don't specify a WHERE clause, your query returns all rows from the table. If you specify more than one table in your query and you haven't included a WHERE clause or a JOIN clause, your query generates a Cartesian product 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 don't use a JOIN clause to perform SQL join operations on multiple tables, the resulting Recordset object won't 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 don't want grouped by a GROUP BY clause.
Use various expressions to determine which records the SQL statement returns. For example, the following SQL statements select 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 specify the criteria argument, date literals must be in U.S. format, even if you're not using the U.S. version of the Jet database engine. For example, May 10, 1994, is written 10/5/94 in the United Kingdom and 5/10/94 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, 1994 in a United Kingdom database, you must use the following SQL statement:
SELECT *
FROM Orders
WHERE ShippedDate = #5/10/94#;
You can also use the DateValue function which is aware of the international settings established by Microsoft Windows. For example, for code in the United States, you can use:
SELECT *
FROM Orders
WHERE ShippedDate = DateValue('5/10/94');
For code in the United Kingdom, use:
SELECT *
FROM Orders
WHERE ShippedDate = DateValue('10/5/94');
See Also
IN Clause, ORDER BY Clause, SELECT Statement, SELECT...INTO Statement, SQL Aggregate Functions.
Specifics (Microsoft Access)
In Microsoft Access, the conditions that you establish in a WHERE clause in SQL view are the same as those you might enter in the Criteria field in the query design grid. If you enter criteria in the query design grid, you can change to SQL view to see the WHERE clause.
Conversely, if you enter a WHERE clause in an SQL statement in SQL view, you can change to Design view to see the criteria in the query design grid, unless you are creating a union query. Union queries can only be viewed in SQL view.
Example
Some of the following examples assume the existence of a hypothetical Salary field in an Employees table.
This example selects the LastName and FirstName fields of each record in which the last name is King.
SELECT LastName, FirstName FROM Employees WHERE LastName = 'King';
This example selects the LastName and FirstName fields for employees whose last names begin with the letter S.
SELECT LastName, FirstName FROM Employees WHERE LastName Like 'S*';
This example selects employees whose salaries are between $20,000 and $30,000, inclusive.
SELECT LastName, Salary FROM Employees
WHERE Salary Between 20000 And 30000;
This example selects employees whose last names fall in alphabetic order between Lon and Tol, inclusive. It doesn't retrieve Tolstoy because Tolstoy follows Tol and therefore is outside the specified range.
SELECT LastName, Salary FROM Employees
WHERE LastName Between 'Lon' And 'Tol';
This example selects orders placed during the first half of 1994.
SELECT OrderID, OrderDate FROM Orders
WHERE OrderDate Between #1-1-94# And #6-30-94#;
This example selects employees who live in Interlaken, New York, or Frankfurt.
SELECT LastName, FirstName, City FROM Employees
WHERE City In ('Interlaken', 'New York', 'Frankfurt');