WHERE Clause (Microsoft Access SQL)

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:

Remarks

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 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 must be in U.S. format, even if you are not using the U.S. version of the Microsoft® Jet 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');

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

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

Example

The following example assumes the existence of a hypothetical Salary field in an Employees table. Note that this field does not actually exist in the Northwind database Employees table.

This example selects the LastName and FirstName fields of each record in which the last name is King.

This example calls the EnumFields procedure, which you can find in the SELECT statement example.

  Sub WhereX()
    Dim dbs As Database, rst As Recordset
    ' Modify this line to include the path to Northwind
    ' on your computer.
    Set dbs = OpenDatabase("Northwind.mdb")
    ' Select records from the Employees table where the
    ' last name is King.
    Set rst = dbs.OpenRecordset("SELECT LastName, " _
        & "FirstName FROM Employees " _
        & "WHERE LastName = 'King';")
    
    ' Populate the Recordset.
    rst.MoveLast
    
    ' Call EnumFields to print the contents of the
    ' Recordset.
    EnumFields rst, 12
    dbs.Close
End Sub

See Also

ALL DISTINCT, DISTINCTROW, TOP Predicates (Microsoft Access SQL)

DELETE Statement (Microsoft Access SQL)

FROM Clause (Microsoft Access SQL)

GROUP BY Clause (Microsoft Access SQL)

HAVING Clause (Microsoft Access SQL)

IN Clause (Microsoft Access SQL)

INNER JOIN Operation (Microsoft Access SQL)

LEFT JOIN, RIGHT JOIN Operations (Microsoft Access SQL)

ORDER BY Clause (Microsoft Access SQL)

SELECT Statement (Microsoft Access SQL)

SELECT…INTO Statement (Microsoft Access SQL)

SQL Aggregate Functions (SQL)

UPDATE Statement (Microsoft Access SQL)