Microsoft Access 2010 Bible

Chapter 4: Selecting Data with Queries

151

Figure 4.20 shows both the query design and the datasheet resulting from the query. This figure

also illustrates one reason you might want to hide a column in a query. There’s no point in display-

ing “Cars” in every row in the third column. In fact, because this query only returns information

about cars, the user can very well assume that every record references a car, and there’s no need to

display a product category in the query. Unchecking the Category field’s Show box in the que-

ry’s design removes Category from the datasheet, making the data easier to understand.

You could enter the criteria expression in any of these other ways:

CARS = CARS “CARS” = “Cars”

By default, Access is not case sensitive, so any form of the word cars works just as well as this que-

ry’s criteria.

Figure 4.20 is an excellent example for demonstrating the options for various types of simple char-

acter criteria. You could just as well enter “Not Cars” in the criteria column, to return all products

that are not cars (trucks, vans, and so on).

Generally, when dealing with character data, you enter equalities, inequalities, or a list of accept-

able values.

This capability is a powerful tool. Consider that you have only to supply an example, and Access

not only interprets it but also uses it to create the query recordset. This is exactly what Query by

Example means: You enter an example and let the database build a query based on the example.

To erase the criteria in the cell, select the contents and press Delete, or select the contents and

right-click Cut from the shortcut menu that appears.

Entering other simple criteria

You can also specify criteria for Numeric, Date, and Yes/No fields. Simply enter the example

data in the criteria field just as you did for text fields. In almost every case, Access understand the

criteria you enter and adjusts to correctly apply the criteria to the query’s fields.

It is also possible to add more than one criteria to a query. For example, suppose that you want to

look only at contacts who live in Connecticut and have been customers since January 1, 2012

(where OrigCustDate is greater than or equal to January 1, 2012). This query requires criteria

in both the State and OrigCustDate fields. To do this, it’s critical that you place both exam-

ples on the same criteria row. Follow these steps to create this query:

1. Create a new query starting with tblCustomers.


2. Add ContactType, FirstName, LastName, State, and OrigCustDate to the
   QBE grid.


3. Enter “ct” or “CT” in the Criteria cell in the State column.


4. Enter >= 01/01/2012 in the Criteria cell in the OrigCustDate column.

Access adds pound sign characters (#) around the date in the criteria box.