Displaying Only Selected Records

So far, you’ve been working with all the records of the tblContacts and tblProducts tables. Most often users want to work only with records conforming to some criteria. Otherwise, too many records may be returned by a query, causing serious performance issues. For example, you may want to look only at contacts that are buyers and not sellers. Access makes it easy for you to specify a query’s criteria.

Understanding selection criteria

Selection criteria are simply filtering rules applied to data as it is extracted from the database. Selection criteria instruct Access which records you want to look at in the recordset. A typical criterion might be “all sellers,” or “only those vehicles that are not trucks,” or “cars with retail prices greater than $45,000.”

Selection criteria limit the records returned by a query. Selection criteria aid the user by selecting only the records a user wants to see, and ignoring all the others.

You specify criteria in the Criteria row of the QBE grid. You designate criteria as an expression. The expression can be as a simple example (like “Trucks” or “Not Trucks”) or can take the form of complex expressions using built-in Access functions.

Entering simple string criteria

Character-type criteria are applied to Text-type fields. Most often, you will enter an example of the text you want to retrieve. Here is a small example that returns only product records where the product type is “Cars”:

Figure 36-18. Specifying character criteria. In this case, because you want to see only cars, you enter “CARS” as the criteria.

[View full size image]

Figure 36-18 shows both the query design and the datasheet resulting from the query. This figure also illustrates one reason you may wish to hide a column in a query. There’s no point in displaying “Cars” and 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’s Show box in the queries design would remove the Category column 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 query’s criteria.

Figure 36-18 is an excellent example for demonstrating the options for various types of simple character 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 values that are acceptable.

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. You can also right-click Paste to revert to the previous content (in this case, a blank cell).

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.

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 are both sellers and buyers (“BOTH” type in the ContactType field), and those contacts have been customers since January 1, 2007 (where OrigCustDate is greater or equal to January 1, 2007). This query requires criteria in both the ContactType and OrigCustDate fields. To do this, it is critical that you place both examples on the same Criteria row. Follow these steps to create this query:

Figure 36-19. Specifying character and date criteria in the same query.

[View full size image]

Access displays records of contacts that are both sellers and buyers that became customers after January 1, 2007—in this example, two contact records.

Access uses comparison operators to compare Date fields to a value. These operators include less than (<), greater than (>), equal to (=), or a combination of these operators. Notice that Access automatically adds pound sign (#) delimiters around the date value. Access uses these delimiters to distinguish between date and text data. The pound signs are just like the quote marks Access added to the “Cars” criteria. Because the OrigCustDate is a DateTime field, Access understands what you want and inserts the proper delimiters for you.