## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

No credit card required

# Making AND and OR Do What You Expect

## Problem

Logic operators are not conceptually difficult to follow, but combining and nesting them does add complexity. If you don't construct complex SQL statements very carefully, they may return incorrect or incomplete results, sometimes without reporting any errors.

## Solution

Logic operators provide the flexibility to construct criteria in any way that suits your requirements. The `AND` operator returns `true` when all conditions are met; the `OR` operator returns `true` as long as one condition is met. In terms of how this applies to SQL construction, `OR` is used to set criteria for which one condition must be met, while `AND` is used to set criteria for which all the conditions must be met. Some examples are presented in Table 1-1.

Table 1-1. Examples of using logic operators

SQL statement

Description

```SELECT DISTINCT State, City,
Count(LastName) AS Customers
FROM tblCustomers
GROUP BY State, City
HAVING State="NY" AND City="Yonkers"```

This gives a count of customers located in Yonkers, NY. Only customer records in which both the state is New York and the city is Yonkers are counted.

```SELECT DISTINCT State, City,
Count(LastName) AS Customers
FROM tblCustomers
GROUP BY State, City
HAVING State="NY"AND City="Yonkers" OR
City="Albany"```

This gives a count of customer records for which the state is New York and the city is either Yonkers or Albany.

This produces an unintended result. The `OR` statement does not properly apply to both Yonkers and Albany. Any Yonkers customers must be in New York, but the way this `SQL` statement is constructed, Albany customers do not have to be in New York. Consequently, as Figure 1-4 shows, customers in Albany, GA will also be returned.

```SELECT DISTINCT State, City,
Count(LastName) AS Customers
FROM tblCustomers
GROUP BY State, City
HAVING State="NY" AND (City="Yonkers" OR
City="Albany")```

This correctly returns customer records for customers located only in Yonkers, NY and Albany, NY. Enclosing the cities and the `OR` operator in parentheses ensures that both cities must also match the state of New York on a record-by-record basis.

## Discussion

`OR` is applied amongst records; `AND` is applied across fields. What does this mean? Figure 1-5 shows the tblCustomers table that is used as the example in this recipe. The `OR` operation involves evaluating the value in a particular field in each record. A single record cannot contain both Albany and Yonkers in its City field; it can contain at most one of those values. So, searching for customers in Albany or Yonkers requires looking for these values in the City field of each record (or, in our example, at least those records in which the state is New York). Thought of another way, when using `OR`, you can apply the statement multiple times to the same field. For example:

`	City="Albany" OR City="Syracuse" Or City="Yonkers"`

The `AND` operator, however, is not used on the same field. A SQL condition like this:

`	City="Albany" AND City="Yonkers"`

would make no sense. No records can be returned because there cannot be any records in which the single City field holds two values. Instead, `AND` is applied to pull together the values of two or more fields, as in:

`	State="New York" AND City="Yonkers"`

The query grid in Access is flexible enough to handle any combination of `OR` and `AND` operators. Figure 1-6 shows how the grid is used to return customer records from New York where the customer type is Retail or Wholesale, as well as customer records from Florida where the customer type is Internet or Mail Order. Internet and Mail Order customers from New York will not be returned, nor will Retail or Wholesale customers from Florida.

Along a single Criteria row, all of the conditions set in the different fields must be met (i.e., this is an `AND` operation). The SQL statement Access generates bears this out:

```	SELECT [FirstName] & " " & [LastName] AS Customer,
City, State, CustomerType
FROM tblCustomers
WHERE
(((State)="NY") AND
((CustomerType)="Retail" Or (CustomerType)="Wholesale"))
OR
(((State)="FL") AND
((CustomerType)="Mail Order" Or (CustomerType)="Internet"))
ORDER BY tblCustomers.CustomerType;```

As you can see, the SQL condition for NY is followed by `AND` to get Retail and Wholesale customers from that state.

## With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

No credit card required