In an introductory Access book that I wrote, I named one chapter "It's Only an Expression." The title might or might not have been catchy, but it certainly didn't do expressions justice. They are essential to your work in Access, and they are indispensable for finding and retrieving records in filters and queries. Whether you are using the Find command to locate a Jane in a First Name field or setting criteria with multiple conditions in advanced queries, you are using expressions to tell Access which records to retrieve.
Unfortunately for the nonmathematical, the dictionary definition of an expression doesn't offer much enlightenment: any combination of mathematical or logical operators, constants, functions, and names of fields, controls, and properties that evaluates to a single value. Half the words in the definition themselves require definition (the sidebar on expression terminology offers some help).
Instead of spending a lot of time on terminology, I think you'll find it more instructive to see some expressions in action. I have categorized them by operator, which tells Access the type of action to perform. Besides the few examples of expressions here, you'll have many more opportunities in the next two chapters to work with expressions in queries.
Let's start with an example using the all-important LIKE operator.
As you can see, the LIKE operator matches the pattern you specify. You can type the operator as like or LIKE or Like or even LiKE; Access displays it as Like.
The expression Like "bronze vase" includes the LIKE operator, which tells Access to perform a specific action. The other element, "bronze vase", is the literal value that Access evaluated. As steps 56 of the example demonstrate, if you just want to match a literal value in Access, you do not need to include an operator.
The Like operator is usually used with wildcards. The expression Like "*d" means "find me values that end in d with any number of characters before it." You'll see many more examples of the Like operator at work when I discuss queries in Chapters 8 and 9.
AND and OR
You used the AND and OR operators earlier in the chapter. You use AND criteria when two or more conditions must be true at the same time, and OR criteria when any of several conditions is sufficient.
Another View of AND and OR
Because AND and OR operators are so important to your Access work, let's look at them from a simple vantage point, just in case you're having trouble distinguishing the two.
Given their importance in Internet search engines, new Access users are often aware of the AND and OR operatorsif not the precise terminology, at least their practical application. Let's use the Advanced Search page at Google for general examples of AND and OR that are not specific to Access.
Say you're looking for information about camping in the Adirondacks. To find that information, you enter camping Adirondacks in the With All of the Words box at the top of the page (see Figure 7.11).
Figure 7.11. Internet surfers have become familiar with the use of AND or OR criteria through search engine forms. When all the words must be found, you're using AND criteria. When any of several wordsthat is, any of several conditionswill be a match, you're using OR criteria.
Why all of the words? If you entered camping alone, you'd get pages on the great outdoors from Timbuktu to Kalamazoo. If you entered just Adirondacks, you'd get pages on biking in the Adirondacks, protecting the Adirondacks, and museums in the Adirondacks. Both keywords are needed for your search.
In this case you're using the AND operator. Two conditionsthe presence of each of the keywordsmust apply to have a match.
Now say you're just looking for general information about either the Adirondacks or the Poconos. You use the At Least One of the Words box to enter Poconos Adirondacks.
Why at least one of the words? A page on either resort area is a good hit, so a single URL need not include both terms. Nevertheless, when you search for at least one of the words, the engine could return pages that have both keywords on the same page.
In this case, you're using the OR operator. Either conditiona match with Adirondacks or Poconoswill do just fine. If both conditions happen to be met on one page, that's okay, too (you don't know whether that's a more useful match until you view the page).
Examples of AND and OR
On the design grid, AND criteria is entered on the same row, and OR criteria is entered on different rows. You'll use AND and OR criteria in the case example at the end of this chapter and in Chapters 8 and 9. But let's do a quick exercise now to make sure you understand the difference.
The NOT operator can be used to reverse any operator. For example, placed before the expression LIKE "laundry", the expression becomes NOT LIKE "laundry"that is, it will retrieve records that don't match the text string laundry. Here's another use of the NOT operator:
The IS operator has very limited use. If you want to look for records with null values, use the expression Is Null. If you don't want to include null values, use Is Not Null.
In frmMerchandise, nine records don't have supplier numbers. Because they are blank, you don't know whether they are null values or zero-length fields.
If you want to match zero-length strings instead, don't use the IS operator; type "" (double quotation marks) in the Criteria row of the applicable field.
I don't think the arithmetic operators +, -, *, and / (for addition, subtraction, multiplication, and division) will cause you any concern. The ^ operator might be unfamiliar; it is used for exponentsraising a number to a specific power.
Note that the asterisk (*) is both an operator and a wildcard. It is a wildcard when used with the LIKE and NOT operators, but it is a multiplication operator when used with calculated expressions such as [ListPrice]*1.08. You'll see arithmetic operators more in the next two chapters.
You've already used comparison operators such as > and <. If you understand that the operator >= means "greater than or equal to," you won't have a problem with other comparison operators, such as =, <, <=, >, and >=. One operator that you might not be familiar with, however, is <>, which means "not equal to." For example, <>100 tells Access to search for values that do not equal 100.
Note that the <> operator differs from NOT, which means "not true." If you want to exclude records with cities that begin with m, enter the expression Not "m*"; don't use <> "m*".
Comparison Operators with Text
As this example indicates, you can use comparison operators with not only numbers and dates, but text as well. Try the following exercise:
The Between...And... operator is often used with dates and numbers. For example, the expression between 3/1/05 and 3/31/05 in a field with a Date/Time data type finds records for all dates in March 2005. You can also use Between...And... for finding a range of names, or a range of prices. For example, between 4 and 22 would find prices between $4 and $22. Note that this expression is the same as >=4 and <=22.
Access uses a pound sign (#) to denote a literal that is a date (as in #3/1/05#) or time (as in #1:00:00#), as compared with the quotation marks used for text strings.
Try this exercise: