Microsoft Access 2010 Bible

Part I: Access Building Blocks

192

The And/Or operators are the most common operators when working with complex criteria. The

operators consider two different expression (one on each side of the And/Or operators) and then

determine whether the expressions are True or False. Then the operators compare the results of

the two expressions against each other for a logical True/False answer. For example, take the

first And statement in the expression given in the preceding paragraph:

(tblCustomers.State = “CA” AND tblProducts.Category <> “Cars”)

The right side of the criteria (tblProducts.Category <> “Cars”) evaluates to True if

Category is anything other than Cars. The And operator compares the logical True/False

from the left and right expressions to return True/False answer.

Note
A field has a Null value when it has no value at all. Null indicates the lack of entry of information in a field.
Null is neither True nor False, nor is it the same as a space character or 0. Null simply has no value. If you
never enter a name in the City field and just skip it, Access leaves the field empty (unless a default value is
provided in the table’s design). This state of emptiness is known as Null.

When the result of an And/Or operation is True, the overall condition is True, and the query

displays the records meeting the True condition.

Notice that the result of an And operation is True only when both sides of the expression are

True, whereas the result of an Or operation is True when either side of the expression is True. In

fact, one side can be a Null value, and the result of the Or operation will still be True if the other

side is True. This is the fundamental difference between And/Or operators.

Using functions in select queries

When you work with queries, you might want to use built-in Access functions to display informa-

tion. For example, you might want to display items such as:

l The day of the week (Sunday, Monday, and so forth) for sales dates

l (^) All customer names in uppercase
l The difference between two date fields
You can display all this information by creating calculated fields for the query.
Cross-Reference
I discuss calculated fields in depth in detail in Chapter 18 (and in many other places in this book).
Referencing fields in select queries
When you work with a field’s name in queries, most often you should enclose the name in square
brackets ([]). Access requires brackets around any field name that’s used as a query’s criteria and