So far, you have learned how to select the fields you want and how to indicate the sort order for your query output. One of the important features of queries is the capability to limit your output by selection criteria. Access allows you to combine criteria by using any of several operators to limit the criteria for one or more fields. The operators and their meanings are covered in Table 4.1.
Operator | Meaning | Example | Result |
|---|---|---|---|
= | ="Sales" | Finds only those records with "Sales" as the field value. | |
< | <100 | Finds all records with values less than 100 in that field. | |
<= | <=100 | Finds all records with values less than or equal to 100 in that field. | |
> | >100 | Finds all records with values greater than 100 in that field. | |
>= | >=100 | Finds all records with values greater than or equal to 100 in that field. | |
<> | <>"Sales" | Finds all records with values other than Sales in the field. | |
And | Created by adding criteria on the same line of the query design grid to more than one field | Finds all records where the conditions in both fields are true. | |
Or | "CA" or "NY" or "UT" | Finds all records with the value of "CA", "NY", or "UT" in the field. | |
Like | Like "Sales*" | Finds all records with the value of "Sales" at the beginning of the field. | |
Between | Between 5 and 10 | Finds all records with the values of 510 (inclusive) in the field. | |
In | In("CA", "NY","UT") | Finds all records with the value of "CA", "NY", or "UT" in the field. | |
Not | Not "Sales" | Finds all records with values other than Sales in the field. | |
Is Null | Is Null | Finds all records where no data has been entered in the field. | |
Is Not Null | Is Not Null |
NOTE
The asterisk (*) is a wild card. Used in the example "Like Sales*", it will return all records that begin with Sales and are followed by any remaining characters.
Criteria entered for two fields on a single line of the query design grid are considered an And, which means that both conditions need to be true for the record to appear in the query output. Entries made on separate lines of the query design grid are considered an Or, which means that either condition can be true for the record to be included in the query output. Take a look at the example in Figure 4.7; this query would output all records in which the ContactTitle field begins with either Marketing or Owner, regardless of the customer ID. It outputs the records in which the ContactTitle field begins with Sales only for the customers whose IDs begin with the letters
M through
Design a query to find all the sales agents in Brazil or France. The criteria you build should look like those in Figure 4.8. |
Notice that the criterion for the Country field is "Brazil" Or "France" because you want both Brazil and France to appear in the query output. The criterion for the ContactTitle field is "Sales Agent". Because the criteria for both the Country and ContactTitle fields are entered on the same line of the query design grid, both must be true for the record to appear in the query output. In other words, the customer must be in either Brazil or France and must also be a sales agent.
Modify the query so that you can output all the customers for whom the contact title begins with Sales. Try changing the criteria for the ContactTitle field to Sales. Notice that no records appear in the query output because no contact titles are just Sales. You must enter "Like Sales*" for the criteria. Now you get the Sales Agents, Sales Associates, Sales Managers, and so on. You still don't see the Assistant Sales Agents because their titles don't begin with Sales. Try changing the criteria to "Like *Sales*". Now all the Assistant Sales Agents appear.