Share this Page URL

Chapter 6. Sorting, Filtering, and Retri... > Designing Select Queries - Pg. 213

Sorting, Filtering, and Retrieving Data 213 would use in a date field to retrieve records with a date in that range. When defining criteria, you aren't limited to using the equal sign. For example, if you wanted to see data about expenses records for categories other than Travel, you could enter <>" Travel ", which means that the category does not equal Travel. This expression is equivalent to using the Filter By Excluding Selection command we described earlier. The date delimiter character When you refer to a date in an expression, you need to wrap the date with the date delimiter character, the pound sign (#). Access provides the pound signs for you in many instances, but you should get in the habit of providing them yourself to avoid ambiguity. The pound signs specify the Date data type. If you don't include the pound sign, Access treats a date as a text string. You use an operator such as the equal sign, the greater than sign, or others when you enter criteria in a query. In addition to standard mathematical, logical, and comparison operators (see Table 3-4, "Operators for Use in Expressions," in Chapter 3), you can use the operators listed in Table 6-1 to craft the criteria you need in a query. Table 6-1. Some Operators for Query Criteria Operator <> (not equal to). Excludes records with the value given. Like. Returns records that closely match the criteria. You can use wild- card characters to manage the scope of the records selected. Examples <> 1 Like "E*" Results Returns records other than those for which the field value equals 1. Returns records with a value that starts with E in the field in which you enter the criteria. Like "Engl???" Return records with values such as English or Eng- land in the field in which you enter the criteria. The values must start with the characters Engl and be followed by three characters (represented by the wildcard character ?). Returns records that begin with E, F, or G. Returns records between January 1, 2004, and June 30, 2004, including those dates. Returns records with the value Advertising, Con- sulting, or Production. Like "[E-G]*" Between. Returns records in the range specified. In. Returns records that match a value in a list you provide. (The In operator provides the same result as the Or op- erator.) Not. Excludes records with the given value. (The Not operator produces the same results as Not Equal To.) Is Null. Returns only Null values. Is Not Null. Returns only values that aren't Null. Between #1/1/04# And #6/30/04# In ("Advertising", "Consulting", "Production") Not "English" Returns records other than those with the value English. Returns records with Null values in the field. Returns records without a Null value in the field. Is Null Is Not Null In the steps that follow, we'll use some of these operators in criteria we add to the expense report query. Add criteria to a query 1. 2. 3. Open qryExpenseReport in Design view. In the Criteria row for the CampaignID field, enter 3. In the row marked Show, clear the check box for the CampaignID field.