Expressions

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.

Expression Terminology

Perhaps the least enjoyable part of using Access is learning abstract terminology that can be cryptic. These terms are important for discussing expressions, however, so here are some definitions:

Operators are symbols that tell you what action will be performed on the expression. Examples include +, -, AND, OR, NOT, >, and <.

Constants include Yes, No, true, False, and Null. These are values that do not change.

Literal values, or literals, are actual values (numbers, dates, names) that Access evaluates exactly as written. Examples abound: pony, 685, Siam, 3/22/96.

Functions return a value based on the results of a calculation or some other expression. The Sum() function, for example, returns the sum of whatever is in the parentheses. Functions are discussed in the next chapter.

Identifiers are elements in an expression that refer to the value of fields, controls, properties, and so on. The format of an identifier depends on the context. For example, the full identifier for a UnitsInStock field in a Products table would be [Products]![UnitsInStock]. But in a query based on the Products table, you can refer to the field simply as [UnitsInStock].

LIKE

Let's start with an example using the all-important LIKE operator.

1.	Open the frmMerchandise form.
2.	Choose Records, Filter, Advanced Filter/Sort.
3.	Click Clear Grid to remove all existing criteria.
4.	Double-click MerchName to add it to the grid.
5.	Type bronze vase on the Criteria row.
6.	Click Apply Filter. Access finds the bronze vase record.
7.	Choose Records, Filter, Advanced Filter/Sort
8.	Edit the criteria to Like "bronze vase" (see Figure 7.10). Figure 7.10. The LIKE operator finds matches for the text string bronze vase. The quotation marks, which signify that the text string between them is a literal value, will already be in place.
9.	Click Apply Filter. The same record is selected.

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.

1.	Choose Records, Filter, Advanced Filter/Sort.
2.	Click Clear Grid to remove all criteria.
3.	Double-click PurchasePrice and UnitsInStock to add them to the grid.
4.	On the Criteria row of the PurchasePrice column, type <10 to find all products that cost less than $10.
5.	On the Criteria row of the UnitsInStock column, type 3 to find products that have three units in stock. You are using AND criteria. Access will find products that cost less than $10 and have three units in stock.
6.	Click Apply Filter. Access finds three products with that criteria.
7.	Choose Records, Filter, Advanced Filter/Sort.
8.	On the Criteria row of the UnitsInStock column, cut 3. Paste it in the Or row of the same column (see Figure 7.12). Figure 7.12. The design grid contains OR criteria because both the Criteria and Or rows contain criteria. You are now using OR criteria. Access will find all records that are either priced at less than $10 or have three units in stock.
9.	Click Apply Filter to see your record set. Your record set includes Item ID #3, which costs more than $10 but is included because there are three units in stock.

NOT

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:

1.	Choose Records, Filter, Advanced Filter/Sort.
2.	Edit the criteria to not <10. The expression not <10 is equivalent to >=10that is, greater than or equal to 10.
3.	Click Apply Filter. Now Access finds all the products with purchase prices equal to or above $10, as well as those records that cost less than $10 but have three units in stock (see Figure 7.13). Figure 7.13. The datasheet now includes products that meet OR criteria, which includes an expression using the NOT operator. (For a better view and easier comparisons, I've hidden most of the columns in the data sheet.)

IS

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.

1.	Choose Records, Filter, Advanced Filter/Sort.
2.	Click Clear Grid.
3.	Double-click SupplierStockNumber to add it to the grid.
4.	Type is null in the Criteria row.
5.	Click Apply Filter. The seven records that have null values are displayed.

NOTE

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.

Arithmetic Operators

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.

Comparison Operators

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:

1.	Choose Records, Filter, Advanced Filter/Sort.
2.	Click Clear Grid.
3.	Double-click the MerchName field.
4.	In the Criteria row, type >=g.
5.	Click Apply Filter.
6.	Select the Product Name column and click the Sort Ascending (AZ) button. Only products that begin with g through z are displayed in the datasheet (see Figure 7.14). Figure 7.14. The use of comparison operators, such as >=, is not limited to numbers and dates. They can also be used with text to find values that begin before or after a letter of the alphabet.

Between...And...

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.

NOTE

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:

1.	Choose Records, Filter, Advanced Filter/Sort.
2.	Click Clear Grid.
3.	Double-click the UnitsInStock field.
4.	In the criteria row, enter between 3 and 7.
5.	Click Apply Filter. Access finds records with three, four, five, six, or seven units in stock.
6.	Close the frmMerchandise form.