Microsoft Access 2010 Bible

Chapter 5: Using Operators and Expressions in Access

199

4. Type Not NY in the cell.

Access automatically places quotation marks around NY if you don’t do so before you leave
the field. You can also use <> instead of the word Not. The query should look like Figure 5.8.
The query selects all records except those for buyers who live in the state of New York.

FIGURE 5.8

Using the Not operator in criteria

Note
You can use the <> operator instead of Not in Step 4 of the previous instructions to exclude New York (NY). The
result is the same with either operator. These two operators are interchangeable except with the use of the key-
word Is. You can’t say Is <> Null. Instead, you must say Not Is Null or more accurately Is Not Null.

Entering numeric criteria

You use numeric criteria with numeric or currency data-type fields. You simply enter the numbers

and the decimal symbol — if required — following the mathematical or comparison operator (but

don’t use commas!). For example, you might want to see all sales where the product’s inventory

count is less than 6:

1. Open a new query in Design view, and add tblProducts.


2. Add ProductID, Description, Make, Model, and QtyInStock from tbl-
   Products to the QBE grid.


3. Click in the Sort cell for Make and select Ascending from the drop-down list.


4. Click in the Criteria cell for QtyInStock and enter <10 in the cell.

Your query looks like Figure 5.9. When working with numeric data, Access doesn’t
enclose the expression with quotes, as it does with string criteria.