In Operator

Description

Determines whether the value of an expression is equal to any of several values in a
specified list.

Syntax

expr [Not] In(value1, value2, . . .)

Remarks

The In operator uses the following arguments.

Argument	Description

expr	Expression identifying the field that contains the data you want to evaluate.
value1, value2	Expression or list of expressions against which you want to evaluate expr.

If expr is found in the list of values, the In operator returns True ( – 1); otherwise, it returns False (0). You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values).

You might use In to determine which orders are shipped to a set of specified regions:

SELECT *
FROM Orders
WHERE ShipRegion In ('Avon','Glos','Som')

See Also

SQL Expressions.

Specifics (Microsoft Access)

In Microsoft Access, you can use the In operator in a query expression or in a calculated control on a form or report.

You can use the In operator in a query expression when you need to set a number of criteria. For example, suppose you have an Orders table with both a ShipCountry field and an OrderID field, and you want to create a query to show all orders sent to the United States, Canada, or the United Kingdom. Create a new query in the Query window and add the Orders table. Drag the OrderID field and the ShipCountry field to the query design grid. Enter the following expression in the Criteria cell below the ShipCountry field.

In('USA', 'Canada', 'UK')

When you run the query, you will see all orders shipped to one of these three countries.

You can achieve the same result by entering the following expression in the Criteria cell.

"USA" Or "Canada" Or "UK"

With a long list of criteria, it may be more convenient to use the In operator than the Or operator. In addition, the SQL statement for the expression containing the In operator is shorter.

You can use the In operator in a calculated control to determine whether the value of a field in the current record is within a set of values. For example, you might use the In operator with the IIf function to determine whether the value of a control is among a set of specified values. In the following example, if the ShipRegion is WA, OR, or ID, the IIf function returns "Local." Otherwise, it returns "Nonlocal."

= IIf([ShipRegion] In ('WA','OR','ID'), "Local", "Nonlocal")

Example

The following example uses the Orders table in the Northwind.mdb database to create a query that includes orders shipped to Avon, Gloucester, and Somerset.

SELECT * FROM Orders WHERE ShipRegion In ('Avon','Glos','Som');