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 syntax has these parts:

Part	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; otherwise, it returns False. You can include the Not logical operator to evaluate the opposite condition (that is, whether expr is not in the list of values).

For example, you can 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, WHERE clause.

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 all orders shipped to Lancashire and Essex and the dates shipped.

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

Sub InX()

    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 Orders table that
    ' have a ShipRegion value of Lancashire or Essex.
    Set rst = dbs.OpenRecordset("SELECT " _
        & "CustomerID, ShippedDate FROM Orders " _
        & "WHERE ShipRegion In " _
        & "('Lancashire','Essex');")

    ' Populate the Recordset.
    rst.MoveLast

    ' Call EnumFields to print the contents of
    ' the Recordset.
    EnumFields rst, 12

    dbs.Close

End Sub

Example (Microsoft Access)

To try the following example in Microsoft Access, first create a new query in the Northwind sample database. Close the Show Table dialog box without specifying a table or query. Switch to SQL view, paste the example into the SQL window, and run the query.

This example creates a query on the Orders table that includes orders shipped to Colchester, Hedge End, and London:

SELECT * FROM Orders WHERE ShipCity In ('Colchester','Hedge End','London');