Refining a Query by Using Criteria
So far in this hour, you have learned how to select the fields you want and how to indicate the sort order for query output. One of the important features of queries is the ability to limit output by using selection criteria. Access allows you to combine criteria by using several operators to limit the criteria for multiple fields. Table 10.1 covers the operators and their meanings.
Table 10.1. Access Operators|
= | Equal to | ="Sales" | Finds only records with "Sales" as the field value. | < | Less than | <100 | Finds all records with values less than 100 in that field. | <= | Less than or equal to | <=100 | Finds all records with or equal tovalues less than or equal to 100 in that field. | > | Greater than or equal to | >100 | Finds all records with values greater than 100 in that field. | >= | Greater than | >=100 | Finds all records with or equal to values greater than or equal to 100 in that field. | <> | Not equal to | <>"Sales" | Finds all records with values other than Sales in the field. | And | Both conditions must be true | Created by adding criteria on the same line of the query design grid to more thanone field | Finds all records where the conditions in both fields are true. | Or | Either condition can be true | "CA" or "NY" or "UT" | Finds all records with the value "CA", "NY", or "UT" in the field. | Like | Compares a string expression to a pattern | Like "Sales*" | Finds all records with the value "Sales" at the beginning of the field (the asterisk is a wildcard character). | Between | Finds a range of values | Between 5 and 10 | Finds all records with the values 5–10 (inclusive) in the field. | In | Same as Or | In("CA","NY","UT") | Finds all records with the value "CA", "NY", or "UT" in the field. | Not | Same as <> | Not "Sales" | Finds all records with values other than Sales in the field. | Is Null | Finds nulls | Is Null | Finds all records where no data has been entered in the field. | Is Not Null | Finds all records that are not null | Is Not Null | Finds all records where data has been entered into thefield. |
Criteria entered for two fields on a single line of the query design grid are considered an And condition, 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 condition, which means that either condition can be true for Access to include the record in the query output. Take a look at the example in Figure 10.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 R, inclusive. Notice that the word Sales is immediately followed by the asterisk. This means that salesman would be included in the output. On the other hand, Marketing and Owner are both followed by spaces. That means that only entries that begin with Marketing or Owner followed by a space are included in the output.
Working with Dates in Criteria
Access gives you significant power for adding date functions and expressions to query criteria. Using these criteria, you can find all records in a certain month, on a specific weekday, or between two dates. Table 10.2 lists the Data criteria expressions and examples.
Table 10.2. Date Criteria Expressions|
Date() | Current date | Date() | Records the current date within a field. | Day(Date) | The day of a date | Day([OrderDate])=1 | Records the order date on the first day of the month. | Month(Date) | The month of a date | Month([OrderDate])=1 | Records the order date in January. | Year(Date) | The year of a date | Year([OrderDate])=1991 | Records the order date in 1991. | Weekday(Date) | The weekday of a date | Weekday([OrderDate])=2 | Records the order date on a Monday. | Between Date And Date | A range of dates | Between #1/1/95# and #12/31/95# | Finds all records in 1995. | DatePart (Interval,Date) | A specific part of a date | DatePart ("q",[OrderDate])=2 | Finds all records in the second quarter. |
The Weekday(Date, [FirstDayOfWeek]) function works based on your locale and how your system defines the first day of the week. Weekday() used without the optional FirstDayOfWeek argument defaults to vbSunday as the first day. A value of 0 defaults FirstDayOfWeek to the system definition. Other values can be set also.
Figure 10.8 illustrates the use of a date function. Notice that DatePart("q",[OrderDate]) is entered as the expression, and the value 2 is entered for the criterion. Year([OrderDate)] is entered as another expression, with the number 1995 as the criterion. Therefore, this query outputs all records in which the order date is in the second quarter of 1995.
Task: Adding Criteria to a Query
 | Design a query to find all the sales agents in Brazil or France. The criteria you build should look like those in Figure 10.9. The steps provide criteria for the contact title and country fields. |
The criterion for the Country field should be "Brazil" Or "France" because you want both Brazil and France to appear in the query output. The criterion for the ContactTitle field should be "Sales Agent". Because you should enter the criteria for both the Country and ContactTitle fields 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 Sales Agent, Sales Associate, Sales Manager, and so on. You still don't see Assistant Sales Agent because their titles don't begin with Sales. Try changing the criteria to "Like *Sales*". Now all Assistant Sales Agent appear. This is because the wildcard, the asterisk, appears both before and after Sales. This means that Access returns anything containing the word Sales anywhere within it. For full coverage of the asterisk wildcard, see Hour 3, "Queries Introduced."
 |
