Chapter 5: Using Operators and Expressions in Access
201
Entering OLE object criteria
You can specify criteria for OLE objects: Is Null or Is Not Null. For example, suppose you
don’t have pictures for all the products and you want to view only those records that have a
picture — that is, those in which the picture is not null. You specify the Is Not Null criterion for
the Picture field of tblProducts.Tip
Although Is Not Null is the correct syntax, you can also use Not Null in the QBE grid and Access supplies
the Is operator for you.
Using Multiple Criteria in a Query
In previous sections of this chapter, you worked with single-condition criteria on a single field. As
you learned in those sections, you can specify single-condition criteria for any field type. In this sec-
tion, you work with multiple criteria based on a single field. For example, you might be interested in
seeing all records in which the buyer comes from New York, California, or Arizona. Or maybe you
want to view the records of all the products sold during the first quarter of the year 2012.The QBE pane has the flexibility to solve these types of problems. You can specify criteria for sev-
eral fields in a select query. Using multiple criteria, for example, you can determine which prod-
ucts were sold for the past 90 days. Either of the following expressions could be used as criteria in
the SaleDate field’s criteria:Between Date() And Date() - 90
Between Date() And DateAdd(“d”,-90,Date())Of these, the expression using the DateAdd function is less ambiguous and more specific to the
task.Understanding an Or operation
You use an Or operator in queries when you want a field to meet either of two conditions. For
example, you might want to see all the records where the customer lives in either New York or
California. In other words, you want to see all records where a customer lives in NY, in CA, or
both. The general expression for this operation is[State] = “NY” Or [State] = “CA”If either side of this expression is True, the resulting answer is also True. To clarify this point,
consider these conditions:l Customer 1 lives in NY — the expression is True.l (^) Customer 2 lives in CA — the expression is True.