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.
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
SELECT DISTINCT State, City, Count(LastName) AS Customers FROM tblCustomers GROUP BY State, City HAVING State="NY" AND City="Yonkers"
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
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 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"
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
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;