The WHERE clause specifies the criteria expression, or a filter condition that records must meet to be included in the query results.

When included, WHERE follows FROM. If a WHERE clause is not specified, the query returns all rows from the table.

SELECT * FROM tableName WHERE filterCondition

in which filterCondition takes the form

fieldName comparator expression

and uses the following operators for comparison:

NOTE: NOT ALL data source providers support ALL the comparison operators. Always test to ensure whether a specific provider supports the comparator.

You can use any valid expression to determine which records the SQL statement returns. The filter condition can take any of the forms in the following examples.

String values are delimited with single quotes

strSQL="SELECT . . . WHERE State='CA'"

while numeric values are stated as numbers that can contain decimals.

strSQL="SELECT . . . WHERE Payments.Amount >= 1000"

Date literals must be in U.S. format. A date literal is any sequence of characters with a valid format that is delimited by a hash sign ( # ).

strSQL="SELECT . . . WHERE Orders.ShipDate <= #12/31/01#"

Use date literals to maximize portability across national languages.

You can include as many filter conditions as needed in a WHERE clause, connecting them with the AND or OR operator. For example, to select all items in the Orders table shipped between January 1, 2000 and December 31, 2000

strSQL="SELECT . . . WHERE ShipDate >= #1/1/00# 
   AND ShipDate <= #12/31/00#"

You can also use the NOT operator to reverse the value of a logical expression.

strSQL="SELECT . . . WHERE State='CA' 
   AND NOT City='Los Angeles'"

Using the WHERE Clause Run Sample | View Source

The sample at right shows how to dynamically pass different values ( strings, dates, or numbers ) to a WHERE clause. To test, select the field and the comparison operator from the appropriate listbox, then enter the value to set up the filter condition.

For example, to return all records that have a UnitPrice greater than or equal to a hundred dollars, select UnitPrice from the Fields dropbox, select >= from the Operators dropbox, then type in 100. Recall that you can use the percent sign ( % ) as a wildcard character on either or both sides of a string value with the LIKE operator.

Using Where . . . In Run Sample | View Source

When the filter condition includes IN, the field must contain one of the values in a given set of values before the record is included in the query results.

For example, to select only the records related to Camping products, you can set a filter condition in which the ProductType can be found IN a comma-separated list of values:

strSQL="SELECT . . . WHERE ProductType 
   IN ( 'Backpack', 'SleepingBag', 'Supplies', 'Tent' )"

You can also use the NOT operator to reverse the outcome of the expression. Here, the field must NOT contain any of the values found IN the set before its record is included in the query results.

strSQL="SELECT . . . WHERE Customer.PostalCode 
   NOT IN ( '98052', '98072', '98034' )