MS-Access / Getting Started

---

Search Condition

Describes a simple or compound predicate that is True, False, or undefined for a given row or group. Use a search condition in the WHERE clause of a SELECT statement, a subquery, a DELETE statement, or an UPDATE statement. You can also use a search condition within the HAVING clause in a SELECT statement. The search condition defines the rows that should appear in the resulting logical table or the rows that should be acted upon by the change operation. If the search condition is True when applied to a row, that row is included in the result.

Syntax

[NOT] {predicate | (<search-condition>)}
  [{AND | OR | XOR | EQV | IMP}
  [NOT] {predicate | (<search-condition>)}]...

Notes: If you include a comparison predicate in the form of <expression> comparison-operator <subquery>, the database returns an error if the subquery returns no rows. The database effectively applies any subquery in a predicate within a search condition to each row of the table that is the result of the previous clauses. The database then evaluates the result of the subquery with regard to each candidate row.

The order of evaluation of the Boolean operators is NOT, AND, OR, XOR (exclusive OR), EQV (equivalence), and IMP (implication). You can include additional parentheses to influence the order in which the Boolean expressions are processed. SQL Server does not support the XOR, EQV, and IMP logical operators.

Using XOR, EQV, and IMP in the Access Query Designer

You can express AND and OR Boolean operations directly by using the design grid. If you need to use XOR, EQV, or IMP, you must create an expression in the Field row, clear the Show check box, and set the Criteria row to <> False.

When you use the Boolean operator NOT, the following holds: NOT (True) is False, NOT (False) is True, and NOT (undefined) is undefined. The result is undefined whenever a predicate references a null value. If a search condition evaluates to False or undefined when applied to a row, the row is not selected. The database returns True, False, or undefined values as a result of applying Boolean operators (AND, OR, XOR, EQV, IMP) against two predicates or search conditions according to the tables.

Example

In a desktop database, to find all products for which the unit price is greater than $100 and for which the category description number is equal to Multi-User or the product has a prerequisite, but not both, enter the following (qxmplXOR):

SELECT tblProducts.ProductID, tblProducts.ProductName,
  tblProducts.CategoryDescription, tblProducts.UnitPrice,
  tblProducts.PreRequisite
 FROM tblProducts
 WHERE tblProducts.UnitPrice>100
  AND ((tblProducts.CategoryDescription = "Multi-User")
  XOR (tblProducts.PreRequisite Is Not Null));

In a project file, to find all products for which the unit price is greater than $100 and for which the category description number is equal to Multi-User or the product has a prerequisite, but not both, enter the following:

SELECT tblProducts.ProductID, tblProducts.ProductName,
  tblProducts.CategoryDescription, tblProducts.UnitPrice,
  tblProducts.PreRequisite
 FROM tblProducts
 WHERE tblProducts.UnitPrice>100
  AND ((tblProducts.CategoryDescription = "Multi-User")
  OR (tblProducts.PreRequisite Is Not Null))
  AND NOT ((tblProducts.CategoryDescription = "Multi-User")
  AND (tblProducts.PreRequisite Is Not Null));

See also DELETE Statement, Expression, HAVING Clause, Predicates (BETWEEN, Comparison, EXISTS, IN, LIKE NULL, and Quantified), SELECT Statement, Subquery, UPDATE Statement, and WHERE Clause in this tutorial.