Adding Criteria
Criteria will be familiar to you if you read Lesson 18, which deals with filters. Criteria enables you to choose which records will appear in your query results. For example, I could limit my list of dogs to those whose birth dates were before 8/5/94.
Filters versus Queries
If the primary reason for creating the query is to filter, you may want to create the filter part first using one of the procedures described in Lesson 18, and then you can save the filter as a query. You can open that query in Query Design view and fine-tune it as needed.
To set criteria for a field that you've added to your query, follow these steps:
Type the criteria you want to use, as shown in Figure 20.2. Table 20.1 provides some examples you could have entered in Figure 20.2 and the subsequent results.
Figure 20.2. Enter criteria into the Criteria row in the appropriate field's column.
In Figure 20.2, Access added # symbols because we are working with a date. For other types of criteria, Access adds other symbols, like quotation marks around a text or number string.
| Enter This | To Get Records Where This Value Is |
|---|---|
| 8/5/93 | Exactly 8/5/93 |
| <8/5/93 | Before 8/5/93 |
| >8/5/93 | After 8/5/93 |
| >=8/5/93 | 8/5/93 or after |
| <=8/5/93 | 8/5/93 or before |
| Not <8/5/93 | Not before 8/5/93 |
| Not >8/5/93 | Not after 8/5/93 |
Text, Too
You can also enter text as a criteria. The < and > (before and after) operators apply to alphabetical order with text. For instance, <C finds text that begins with A or B.
Did you notice the "or" row under the Criteria row in Figure 20.2? You can enter more criteria using that line. The query will find records where any of the criteria is true. When you enter criteria into the "or" row, another "or" row appears, so you can enter more.
What About "And"?
When you have two criteria that must both be true, you can put them together in a single Criteria row, with the word "And." For instance, you might want birth dates that were between 12/1/93 and 12/1/95. It would all be in a single Criteria row, like this: >12/1/93 And <12/1/95.