MS-Access / Getting Started

---

IN Predicate

Determines whether a value is equal to any of the values or is unequal to all values in a set returned from a subquery or provided in a list of values.

Syntax

<expression> [NOT] IN {(<subquery>) |
  ({literal},...) |<expression>}

Notes: Comparison of strings in Access or a default installation of SQL Server is case-insensitive. The data types of all expressions, literals, and the column returned by the subquery must be compatible. If the expression is Null or any value returned by the subquery is Null, the result is undefined. In terms of other predicates, <expression> IN <expression> is equivalent to the following:

<expression> = <expression>

<expression> IN (<subquery>) is equivalent to the following:

<expression> = ANY (<subquery>)

<expression> IN (a, b, c,...)

where a, b, and c are literals, is equivalent to the following:

(<expression> = a) OR (<expression> = b) OR
(<expression> = c) ...

<expression> NOT IN ... is equivalent to the following:

NOT (<expression> IN ...)

Examples

To test whether StateOrProvince is on the west coast of the United States, enter the following:

[StateOrProvince] IN ('CA', 'OR', 'WA')

To list all contacts who have not purchased a multi-user product, enter the following (qxmplContactsNotMultiUser):

SELECT tblContacts.ContactID, tblContacts.FirstName,
  tblContacts.MiddleInit, tblContacts.LastName
 FROM tblContacts
 WHERE tblContacts.ContactID NOT IN
  (SELECT ContactID
   FROM tblContactProducts
    INNER JOIN tblProducts
    ON tblContactProducts.ProductID = tblProducts.ProductID
   WHERE tblProducts.CategoryDescription = 'Multi-User');