Chapter 5: Using Operators and Expressions in Access
In previous chapters, you created queries using selected fields from one or more tables. You also sorted the data and set criteria to limit the results of a query. This chapter focuses on using operators and expressions to calculate information, compare values, and display data in a different format—using queries to build examples.
You aren’t limited to using operators and expressions inside of queries. You’ll also learn how to use them when creating calculated fields on forms and reports, and when programming in Visual Basic for Applications (VBA). This chapter only uses queries to demonstrate the use of operators and functions.
This chapter will use the Chapter05Start.accdb database. If you haven’t already copied it onto your machine from the CD, you’ll need to do so now.
For more on using operators and expressions on forms, reports, and in VBA, see Chapters 4, 9, and 13.
What Are Operators?
Operators let you add numbers, compare values, put text strings together, format data, and perform a wide variety of tasks. You use operators to inform Access to perform a specific action against one or more items. The combination of operators and items is known as an expression.
You’ll use operators every time you create an equation in Access. For example, operators specify data-validation rules in table properties, create calculated fields in forms and reports, and specify criteria in queries.
Operators indicate that an operation needs to be performed on one or more items. Some common examples of operators are:
=
&
And
Like
+
Types of operators
Here are the types of operators discussed in this chapter:
• Mathematical (arithmetic) operators
• Relational operators
• String operators
• Boolean (logical) operators
• Miscellaneous operators
Mathematical operators
There are seven basic mathematical operators. These are also known as arithmetic operators, because they’re used for performing numeric calculations:
By definition, you use mathematical operators to work with numbers. When you work with mathematical operators, numbers can be any numeric data type. The number can be an actual number (constant value), the value of a memory variable, or a field’s contents.. Furthermore, you use these numbers individually or combine them to create complex expressions. Some of the examples in this section may seem complex, but trust us: You don’t need a master’s degree in mathematics to work through them.
The * (multiplication) operator
A simple example of when to use the multiplication operator is to calculate the total price of purchasing several items. You could design a query to display the number of items purchased and the price for each item. Then you could add a column—a calculated field—containing the value of the number of items purchased times the price per item. In this case, you could get that information from tblSalesLineItems, and the formula would be [tblSalesLineItems].[Quantity] * [tblSalesLineItems].[Price].
The standard Access notation for dealing with table names and field names in an expression is to enclose them in square brackets.
Notice that you use the table name before the field name in the above example. Because your tables only have one field named Price and one field named Quantity, you could have skipped the table names; however, it’s good practice to specify the name of the table where the field comes from, separating the table name from the field name by a single period.
The + (addition) operator
If you want to create a calculated field in a query for adding the value of tax to the price, you would use an expression similar to this: [TaxAmt]+[tblSalesLineItems].[Price]. To use this expression, you would have to create another calculated field in the query named [TaxAmt] that you create using the multiplication operator—TaxAmt: [tblSales].[TaxRate] * [tblSalesLineItems].[Price]. You could also create a form for adding the values in fields, such as GrossAmount and Tax, in which case you would use the expression [GrossAmount] + [Tax]. This simple formula uses the addition operator to add the contents of both fields and display the result in the object containing the formula.
Besides adding two numbers, you can use the addition operator to concatenate two character strings—putting two text-based strings together to form a single text string. For example, you may want to combine the fields FirstName and LastName from tblContacts to display them as a single field. This expression is:
[tblContacts].[FirstName] + [tblContacts].[LastName]
You use the table name before the field name. In this example, specifying the table name isn’t necessary because your tables only have one field named FirstName and one field named LastName; however, it is good practice to specify the table name containing this field, separating the table name from the field name by a single period.
Although you can concatenate (join) text strings by using the addition operator, you should use the ampersand (&) operator to avoid confusing Access. You can find more on this in the “String operators” section, later in this chapter.
The – (subtraction) operator
An example of using the subtraction operator on the same form is to calculate the final invoice amount by subtracting a calculated discount from the price. The formula to determine the net invoice amount of an item would be as follows:
[tblSalesLineItems].[Price] - ([tblSalesLineItems].[Price] * [tblSalesLineItems].[DiscountPercent])
Although parentheses are not mathematical operators, they play an integral part in working with operators, as we discuss in the “Operator precedence” section, later in this chapter.
The / (division) operator
You can use the division operator to divide two numbers and (as with the previous operators) display the result wherever you need it. Suppose, for example, that a pool of 212 people win the $1,000,000 lottery this week. The formula to determine each individual’s payoff of $4,716.98 per person would be as follows:
1,000,000 / 212
The \ (integer division) operator
The integer division operator takes any two numbers (number1 and number2), rounds them up or down to integers, divides the first by the second (number1 / number2), then drops the decimal portion of the result, leaving only the integer value. Here are some examples of how integer division differs from normal division:
Access rounds numbers based on the greater-than-0.5 rule: Any number with a decimal value of x.5 or less rounds down; greater than x.5 rounds up to the next whole number. This means that 6.49 and 6.5 become 6, but 6.51 and 6.6 become 7.
The integer divide operator can be a confusing operator until you understand just what it does. Using it is equivalent to rounding both numbers in the division operation (101.9 = 102 and 6.6 = 7), then converting the answer to an integer (102 / 7 =14.571 = 14). Remember: It only rounds the numbers in the expression. It does not round the answer; it simply drops the remainder after the decimal point.
The ^ (exponentiation) operator
The exponentiation operator (^) raises a number to the power of an exponent. Raising a number simply means indicating the number of times that you want to multiply a number by itself. For example, multiplying the value 4 x 4 x 4 (that is, 43) is the same as entering the formula 4^3.
The Mod (Modulo) operator
The modulo operator (mod), or remainder operator, takes any two numbers (number1 and number2), rounds them up or down to integers, divides the first by the second (number1 / number2), and then returns the remainder. Here are some examples of how modulo division compares to normal division:
Relational operators
There are six basic relational operators (also known as comparison operators). They compare two values or expressions via an equation. The relational operators include the following:
The expressions built from relational operators always return either a logical value or Null; the value they return says Yes (True), No (not True; that is, False), or Null (unknown/no value).
Access actually returns a numeric value for relational operator equations. It returns a –1 (negative 1) for True and a 0 (zero) for False.
If either side of an equation is a Null value, the result will always be a Null.
The = (equal) operator
The equal operator returns a logical True if the two expressions being compared are the same. Here are two examples of the equal operator:
The <> (not-equal) operator
The not-equal operator is exactly the opposite of the equal operator. In this example, the car example is changed to not-equal:
Notice that you have two different ways to express not equal to: The <> or != symbols mean exactly the same thing.
The < (less-than) operator
The less-than operator returns a logical True if the left side of the equation is less than the right side, as in this example:
The <= (less-than-or-equal-to) operator
The less-than-or-equal-to operator returns a True if the left side of the equation is either less than or equal to the right side, as in this example:
Notice, in the second example, that you got the same results using the operator !> (not greater than). In other words, less than or equal to can be expressed using either operator, <= or !>.
Access 2007 is sensitive to the order of the operators. Access reports an error if you enter =<; the order is important. It must be less than or equal to (<=).
The > (greater-than) operator
The greater-than operator is the exact opposite of the less-than operator. This operator returns a True when the left side of the equation is greater than the right side, as in this example:
The >= (greater-than-or-equal-to) operator
The greater-than-or-equal-to operator returns a True if the left side of the equation is either greater than or equal to the right side. For example:
Notice, in the second example, that you got the same results using the operator !< (not less than). In other words, greater than or equal to can be expressed using either operator, >= or !<.
Access 2007 is sensitive to the order of the operators. Access reports an error if you enter =>; the order is important. It must be greater than or equal to (>=).
String operators
Access has three string operators, also know as text operators. Unlike the other operators, these work specifically with the Text data type:
The & (concatenation) operator
The concatenation operator concatenates or joins two or more values into a single string. This operator works similarly to the addition operator. Unlike the addition operator, however, the & operator always returns a string value. For instance, this example produces a single string:
[FirstName] & [LastName]
However, in the resultant string, no spaces are automatically added. If [FirstName] equals “Fred” and [LastName] equals “Smith”, concatenating the field contents yields FredSmith. To add a space between the strings, you must add a string containing a space between the two fields:
[FirstName] & “ “ & [LastName]
This concatenation operator easily joins a string with a number- or date-type value. Using the & eliminates the need for special functions to convert numbers or dates to strings.
Suppose, for example, that you have a Number field, which is HouseNumber, and a Text field, which is StreetName, and that you want to build an expression that combines both fields. For this, enter the following:
[HouseNumber] & “ “ & [StreetName]
If HouseNumber has a value of 1600 and StreetName is Pennsylvania Avenue N.W., the resulting string is:
“1600 Pennsylvania Avenue N.W.”
Perhaps on a report you want to print the OperatorName field and the date and time the report was run at the bottom of the page. This can be accomplished by creating a calculated field using syntax similar to the following:
“This report was printed “ & Now() & “ by “ & [OperatorName]
Notice the spaces after the word printed and before and after the word by. If the date is March 21, 2007, and the time is 4:45 p.m., the expression looks something like this:
This report was printed 3/21/07 4:45:40 PM by Jim Simpson
Knowing how the concatenation operator works makes maintaining your database expressions easier. If you always use the concatenation operator (&)—instead of the addition operator (+)—for creating concatenated text strings, you won’t have to be concerned with the data types of the concatenated objects. Any formula that uses the & operator converts all the objects being concatenated to a string data type for you. Using the plus sign (+) to concatenate strings can sometimes lead to unpredictable results because Access must decide whether the operands are numbers or strings, and act accordingly. The concatenation operator forces Access to treat the operands as strings and always returns a string as a result.
If both operands are Null, the result is also a Null. If only one of the two objects is Null, Access ignores the Null object and builds the string from the other operand.
The Like (similar to) and Not Like operators
The Like operator, and its opposite, the Not Like operator, are used to compare two string expressions. These operators determine whether one expression matches, or doesn’t match, the pattern of another expression. The resultant value of the comparison is a True, False, or Null.
The Like operator uses the following basic syntax:
expression Like pattern
Like looks for the expression in the pattern; if it is present, the operation returns a True. For example:
If either expression in the Like formula is a Null, the result is a Null.
This operator provides a powerful and flexible tool for string comparisons. Wildcard characters also increase the flexibility of the Like operator. (see the sidebar “Using Wildcards”).
Here are some examples that use wildcards with the Like operator:
If the pattern you’re trying to match actually contains a wildcard character, you must enclose the wildcard character in brackets. In the example:
“AB*Co” Like “AB[*]C*”
the [*] in the third position of the pattern object will look for the asterisk as the third character of the string. Since the asterisk character is enclosed in brackets, it won’t be mistaken for the asterisk wildcard character.
Boolean (logical) operators
Access uses six Boolean operators. Also referred to as logical operators, you use these for creating multiple conditions in expressions. Like relational operators, these always return either a logical True or False or a Null. Boolean operators include the following:
The And operator
You use the And operator to perform a logical conjunction of two expressions; the operator returns the value True if both expressions return True values. The general syntax of an And operation is:
expression 1 And expression 2
Here is an example:
If the expressions on both sides of the And operator are True, the result is a True value. Table 5-1 demonstrates the results.
The Or operator
The Or operator is used to perform a logical disjunction of two expressions; the operator returns the value True if either condition returns a True value. The general syntax of an Or operation is
expression 1 Or expression 2
The following two examples show how the Or operator works:
If the condition of either side of the Or operator is true, the operator returns a True value. Table 5-2 demonstrates the results.
The Not operator
The Not operator is used for negating a numeric or boolean expression. The Not operator returns the value True if the expression is False and False if the expression is True. This operator reverses the logical result of an expression.
The general syntax of a Not operation is:
Not [numeric|boolean] expression
The following examples show how to use the Not operator:
If the operand is Null, the Not operator returns Null. Table 5-3 demonstrates the results.
Miscellaneous operators
Access has three very useful miscellaneous operators:
The Between...And operator
Use the Between...And operator to determine whether one expression’s value is within a specific range of values. This is the general syntax:
expression Between value 1 And value 2
If the value of the expression is between value 1 and value 2, or equal to value 1 or value 2, the result is True; otherwise, it is False.
The following examples show how to use the Between...And operator:
The In operator
The In operator is used to determine whether one expression’s value is equal to any value in a specific list. This is the general syntax:
expression In (value1, value2, value3, ...)
If the value is found in the list, the result is True; otherwise, the result is False.
The following example uses the In operator as a field’s criteria for a query:
In (‘SUV’,’Minivans’)
This displays only those vehicles that are SUVs or minivans.
The Is (reserved word) operator
The Is operator is used only with the keyword Null to determine whether an object has nothing in it. This is the general syntax:
expression Is Null
The following example uses the Is operator:
To eliminate records from a query where a particular field doesn’t contain data, enter Is Not Null as the criteria for that field.
Operator precedence
When you work with complex expressions that have many operators, Access must determine which operator to evaluate first, and then which is next, and so forth. To do this, Access has a built-in predetermined order, known as operator precedence. Access always follows this order unless you use parentheses to specify otherwise.
Parentheses are used to group parts of an expression and override the default order of precedence. Operations within parentheses are performed before any operations outside of them. Inside the parentheses, Access follows the predetermined operator precedence.
Precedence is determined first according to category of the operator. The operator rank by order of precedence is:
1. Mathematical
2. Comparison
3. Boolean
Each category contains its own order of precedence, which we explain next.
The mathematical precedence
Within the general category of mathematical operators, this order of precedence is in effect:
1. Exponentiation
2. Negation
3. Multiplication and/or division (left to right)
4. Integer division
5. Modulo
6. Addition and/or subtraction (left to right)
7. String concatenation
The comparison precedence
Comparison operators observe this order of precedence:
1. Equal
2. Not equal
3. Less than
4. Greater than
5. Less than or equal to
6. Greater than or equal to
7. Like
The Boolean precedence
The Boolean operators follow this order of precedence:
1. Not
2. And
3. Or
4. Xor
5. Eqv
6. Imp
Precedence Order
Simple mathematics provides an example of order of precedence. Remember that Access performs operations within parentheses before operations that are not in parentheses. Also remember that multiplication and division operations are performed before addition or subtraction operations.
For example, what is the answer to this simple equation?
X=10+3*4
If your answer is 52, you need a better understanding of precedence in Access. If your answer is 22, you’re right. If your answer is anything else, you need a calculator!
Multiplication is performed before addition by the rules of mathematical precedence. Therefore, the equation 10+3*4 is evaluated in this order: 3*4 is performed first, which yields an answer of 12. Twelve is then added to 10, which yields 22.
Look at what happens when you add parentheses to the equation. What is the answer to this simple equation?
X=(10+3)*4
Now the answer is 52. Within parentheses, the values 10 and 3 are added first; then the result (13) is multiplied by 4, which yields 52.
Moving beyond Simple Queries
Select queries are the most common type of query used; they select information (based on a specific criterion) from one or more related tables. With these queries, you can ask questions and receive answers about information that’s stored in your database tables. In previous chapters, you work with queries that use simple criteria on a single field in a table with operators, such as equal (=) and greater than (>).
Knowing how to specify criteria is critical to designing and writing effective queries. Although queries can be used against a single table for a single criterion, many queries extract information from several tables using more complex criteria.
Because of this complexity, your queries are able to retrieve only the data you need, in the order that you need it. You may, for example, want to select and display data from the Access Auto Auctions database to get the following information:
• All buyers of Chevy cars or Ford trucks
• All buyers who have purchased something during the past 60 days
• All sales for items greater than $90,000
• The number of customers from each state
• Any customers that have made comments or complaints
As your database system evolves, you’ll want to retrieve a subset of information like this. Select queries are the easiest way to obtain this information from one or more tables. Using operators and expressions, you create complex criteria to limit the number of records and calculated fields that display data differently than it’s stored. This section uses select queries to demonstrate the use of these operators and expressions. Later, you’ll apply this knowledge when working with forms, reports, and VBA code.
Chapter 4 gives an in-depth explanation of working with queries. For more on using operators and expressions on forms, reports, and in VBA, see Chapters 4, 9, and 13.
Using query comparison operators
When working with select queries, you may need to specify one or more criteria to limit the scope of information shown. You specify criteria by using comparison operators in equations and calculations. The categories of operators are mathematical, relational, logical, and string. In select queries, operators are used in either the Field: or Criteria: cell of the QBE (Query by Example) pane.
Here’s a good rule of thumb to observe:
Use mathematical and string operators for creating calculated fields; use relational and logical operators for specifying criteria.
We discuss calculated fields in the “Creating a New Calculated Field in a Query” section, later in this chapter. You can find an in-depth explanation of operators in the “What Are Operators?” section, earlier in this chapter.
Table 5-4 shows most of the common operators that are used with select queries.
Using these operators, you can ferret out groups of records like these:
• Product records that include a picture
• A range of records, such as all sales between November and January
• Records that meet both And and Or criteria, such as all records that are cars and are not either a minivan or SUV
• All records that do not match a value, such as any category that is not a car
When you add a criterion to a query, you use the appropriate operator with an example of what you want. In Figure 5-1, the example is Cars. The operator is equal (=). Notice that the equal sign is not shown in the figure. The equal sign is the default operator for selection criteria.
Figure 5-1
The QBE pane shows a simple criterion asking for all vehicles where the Category is Cars.
Understanding complex criteria selection
You build complex query criteria using any combination of the available operators shown in Table 5-4. For many queries, complex criteria consist of a series of Ands and Ors, as in these examples:
• State must be Connecticut or Texas
• City must be Sunnyville and state must be Georgia
• State must be MA or MO and city must be Springfield
These examples demonstrate the use of both logical operators: And/Or. Many times, you can create complex criteria by entering example data in different cells of the QBE pane. Figure 5-2 demonstrates how to create complex And/Or criteria without entering the operator keywords And/Or at all. This example displays all the buyers and their sales that satisfy the following criteria:
Live in either the state of Connecticut (CT) or the state of Massachusetts (MA) and whose product category is not a car.
You learn how to create this type of complex query in the “Entering Criteria in Multiple Fields” section, later in this chapter.
Figure 5-2
Creating complex And/Or criteria by example without using the And/Or operators. This Query uses both the Criteria row and the Or row of the QBE pane to combine the And/Or criteria through example.
In the QBE pane, enter And criteria in the same row; enter Or criteria in different rows.
Access takes your graphical query and creates a single SQL SELECT statement to actually extract the information from your tables. Click the drop-down in the ribbon’s View group and select SQL View to change the window’s contents to display the SQL SELECT statement (shown in Figure 5-3), which Access creates from the fields and criteria placed in the QBE pane in Figure 5-2.
Figure 5-3
The SQL view for the query built in Figure 5-2. Notice that it contains a single OR statement and two AND statements (in the WHERE clause).
The SQL statement in Figure 5-3 has been separated by the author for clarification purposes. When you switch to SQL View in your database, you’ll see one long multi-line statement with no breaks between sections.
Sometimes you see a field name referred to first by the table name and then by the field name, as shown in the SQL statement in Figure 5-3. When you see this kind of reference, it will have a dot (.) between the two names, such as Customers.[Customer Name]. This nomenclature tells you which table contains the field. This is especially critical when you’re describing two fields that have the same name but are contained in different tables. In a multiple-table query, you see this format in the field list when you add a field to the QBE pane by clicking an empty column. You also see this format when you create a multiple-table form by using the field list. The general format is Table Name.Field Name. If the field name or table name contains spaces, you must surround the name with brackets [ ]; for example, tblSales.[Date of Sale] and tblContacts.[Customer Last Name].
We do not use spaces in table and field names; although many people do use spaces for better readability, it’s a really good idea to not use spaces at all. This way, you don’t have to use brackets around your field or object names. For example, name tblContacts.[Customer Last Name] without spaces between the words as tblContacts.CustomerLastName— eliminating the need for using brackets. Instead of using spaces, capitalize the first letter of each new word in the table and field name (e.g., tblSalesLineItems, FirstName, ZipCode, AuctionEndDate).
If you build an expression for this query (not the SQL statement), it looks similar to this example:
(tblContacts.State = “CT” AND tblProducts.Category <> “Cars”) OR (tblContacts.State = “MA” AND tblProducts.Category <> “Cars”)
You must enter the category (<> “Cars”) for each state line in the QBE pane, as shown in Figure 5-2. In the “Entering Criteria in Multiple Fields” section, later in this chapter, you learn to use the And/Or operators in a Criteria: cell of the query, which eliminates the redundant entry of these fields.
In this example, you looked for all vehicles that didn’t contain cars in the category field. To find records that match a value, drop the use of the <> operator with the value. For example, enter the expression Cars to find all vehicles that have a category of cars. You don’t have to use the equal (=) operator in the QBE pane when looking for matching values.
The And/Or operators are the most commonly used operators when working with complex criteria. The operators consider two different formulas (one on each side of the And/Or operators) and then determine individually whether they are True or False. Then the operators compare the results of the two formulas against each other for a logical True/False answer. For example, take the first And statement in the formula given in the preceding paragraph:
(tblContacts.State = “CT” AND tblProducts.Category <> “Cars”)
The first half of the formula, tblContacts.State = “CT”, converts to a True if the state is CT (False if a different state; Null if no state was entered in the field).
Then the second half of the formula, tblProducts.Category">tblProducts.Category <> “Cars”, is converted to a True if the Category is anything except Cars (False if Cars; Null if no category was entered). The And compares the logical True/False from each side against the other side to return a True/False answer.
A field has a Null value when it has no value at all; it is the lack of entry of information in a field. Null is neither True nor False; nor is it equivalent to all spaces or zero—it simply has no value. If you never enter a city name in the City field and just skip it, Access leaves the field empty. This state of emptiness is known as Null.
When the result of an And/Or operation is True, the overall condition is True, and the query displays those records meeting the True condition. Table 5-5 reviews the True and False conditions for each operator.
Notice that the result of an And operation is True only when both sides of the formula are True, whereas the result of an Or operation is True when either side of the formula is True. In fact, one side can be a Null value, and the result of the Or operation will still be True if the other side is True. This is the difference between And/Or operators.
Using functions in select queries
When you work with queries, you may want to use built-in Access functions to display information. For example, you may want to display items such as:
• The day of the week (Sunday, Monday, and so forth) for sales dates
• All customer names in uppercase
• The difference between two date fields
You can display all this information by creating calculated fields for the query. We discuss calculated fields in depth later in this chapter.
Referencing fields in select queries
When you work with a field name in queries, as you do with calculated fields or criteria values, you should enclose the field name in square brackets ([]). Access requires brackets around any field name that is in a criterion and around any field name that contains a space or punctuation. An example of a field name in brackets is the criterion [tblSales].[SaleDate] + 30.
If you omit the brackets ([ ]) around a field name in the criterion, Access may automatically place quotes around the field name and treat it as text instead of a field name.
Entering Single-Value Field Criteria
You’ll encounter situations in which you want to limit the query records returned on the basis of a single field criterion, such as in these queries:
• Customer (buyer) information for customers living in the state of New York
• Sales of any motor homes
• Customers who bought anything in the month of January
Each of these queries requires a single-value criterion. Simply put, a single-value criterion is the entry of only one expression in a field. That expression can be example data, such as NY, or a function, such as DatePart(“m”,[SaleDate]) = 1. Criteria expressions can be specified for any data type: Text, Numeric, Date/Time, and so forth. Even OLE Object and Counter field types can have criteria specified.
All the examples in this chapter rely on several tables: tblContacts, tblSales, tblSalesLineItems, and tblProducts. The Chapter05Start.accdb database contains the tables used in this chapter. The majority of these examples use only the tblContacts and tblSales tables.
Each series of steps in this chapter tells you which tables and fields make up the query. For most examples, you should clear all previous criteria. Each example focuses on the criteria line of the QBE pane. Examine each figure closely to make sure you understand the correct placement of the criteria in each example.
Entering character (Text or Memo) criteria
You use character criteria for Text or Memo data-type fields. These are either examples or patterns of the contents of the field. To create a text criterion to display customers who live in New York state, for example, follow these steps:
1. Open a new query in Design View based on tblContacts and add the FirstName, LastName, and State fields to the QBE pane.
2. Click the Criteria: cell for State field.
3. Type NY in the cell.
Your query should look similar to the query shown in Figure 5-4. Notice that only one table is open and only three fields are selected. Click the Datasheet View command in the Home ribbon’s Views group to see the results of this query.
Figure 5-4
The Datasheet window showing tblContacts open. You see the example data NY in the Criteria row under the State field.
When specifying example-type criteria, it isn’t necessary to match capitalization. Access defaults to case-insensitive when working with queries. Entering NY, ny, or nY provides the same results.
You don’t have to enter an equal sign before the literal word NY because Access uses the equal operator as the default operator. To see all states except Ny, you must enter either the <> (not equal) or the Not operator before the word NY.
You also don’t have to type quotes around the word NY. Access assumes that you’re using an example literal NY and adds the quotes for you automatically.
If you type quotation marks, you should use the double quotation mark to surround literals. Access normally uses the single quotation mark as a remark character in its programming language. However, when you use the single quotation mark in the Criteria: cell, Access interprets it as a double quotation mark.
The Like operator and wildcards
In previous sections, you worked with literal criteria. You specified the exact field contents for Access to find, which was “NY” in the previous example. Access used the literal to retrieve the specific records. Sometimes, however, you know only a part of the field contents, or you may want to see a wider range of records on the basis of a pattern. For example, you may want to see all buyer information for those buyers who bought vehicles made in the 1950s (where descriptions begin with the characters 195); so you need to check 1950, 1951, 1952, and so forth. Here’s a more practical example: Suppose you have a buyer who has purchased a couple of red cars in the last year. You remember making a note of it in the Comments field about the color, but you don’t remember which customer it was. To find these records, you’re required to use a wildcard search against the Comments field in tblProducts to find any records that contain the word Red.
Access uses the string operator Like in the Criteria: cell of a field to perform wildcard searches against the field’s contents. Access searches for a pattern in the field; you use the question mark (?) to represent a single character or the asterisk (*) for several characters. (This works just like filenames at the DOS level.) In addition to these two characters (? and *), Access uses three other characters for wildcard searches. Table 5-6 lists the wildcards that the Like operator can use.
The question mark (?) stands for any single character located in the same position as the question mark in the example expression. An asterisk (*) stands for any number of characters in the same position in which the asterisk is placed. Access can use the asterisk any number of times in an example expression. The pound sign (#) stands for any single digit (0-9) found in the same position as the pound sign. The brackets ([]) and the list they enclose stand for any single character that matches any one character in the list located within the brackets. Finally, the exclamation point (!) inside the brackets represents the Not word for the list—that is, any single character that does not match any character in the list within the brackets.
These wildcards can be used alone or in conjunction with each other. They can even be used several times within the same expression. The examples in Table 5-6 demonstrate how you can use the wildcards.
To create an example using the Like operator, let’s suppose that you want to find the record of a sports car with an exterior color of red. You know that the word Red is used in one of the records in the Features field of tblProducts. To create the query, follow these steps:
1. Add the four tables: tblContacts, tblSales, tblSalesLineItems, and tblProducts.
2. Select LastName and FirstName from tblContacts, and select Description and Features from tblProducts, and add them to the QBE pane.
Although not necessary, you may want to set an Ascending sort order in the LastName and FirstName fields.
3. Click the Criteria: cell of the Features field.
4. Type * red * in the cell.
Be sure to put a space between the first asterisk and the r and the last asterisk and the d—in other words, put spaces before and after the word red.
In the preceding steps, you put a space before and after the word red. If you did not, Access would find all words that have the word red in them, like aired, bored, credo, fired, geared, restored, and on and on. By placing a space before and after the word red, Access is being told to look for the word red only. Of course, it would not find black/red or red/black with spaces around the word. If you need to find these, you could put them as additional criteria in the or cells.
When you click outside the Criteria: cell, Access automatically adds the Like operator and the quotation marks around the expression. Your query QBE pane should look similar to the one shown in Figure 5-5.
Figure 5-5
Using the Like operator with a select query in a Memo field. In this case, the query looks for the word red in the Features field.
After creating this query, click on the Datasheet View command to view the query’s results. It should look similar to the one shown in Figure 5-6.
Figure 5-6
The results of using the Like operator with a select query in a Memo field; the query looks for the word red in the Features field.
To make your query look like the one shown in Figure 5-6, you need to widen the Description and Features fields to see more of the contents and expand the number of lines to show for each record. To make the height of each record more than one line, select the line between any two records in the record selector bar, as shown in Figure 5-6, between the first and second record. When the cursor becomes a small line with arrows pointing up and down, click and drag the field down to make each record show more lines.
Clicking on the Datasheet View command on the ribbon, you see that eight records match your query request—a red vehicle. Looking closer at the results, you see that although there are eight records that match your criteria of the word red in the Features field, they do not all show red exterior color cars. In this case, you will have to physically examine each record to see if the exterior color of the vehicle is red (versus the interior being red). If you need to see records where the Features may show black/red or red/black, you will need to refine your search. These records are only those that have the standalone word red.
Access automatically adds the Like operator and quotation marks if you meet these conditions:
• Your expression contains no spaces.
• You use only the wildcards ?, *, and #.
• You use brackets ([ ]) inside quotation marks (“ “).
If you use the brackets without quotation marks, you must supply the Like operator and the quotation marks.
Using the Like operator with wildcards is the best way to perform pattern searches through memo fields. It is just as useful in text and date fields as the examples in Table 5-7 demonstrate.
Table 5-7 shows several examples that can be used to search records in the tables of the Access Auto Auctions database.
Specifying non-matching values
To specify a non-matching value, you simply use either the Not or the <> operator in front of the expression that you don’t want to match. For example, you may want to see all contacts who have purchased a vehicle , but you want to exclude buyers from New York state. Follow these steps to see how to specify this non-matching value:
1. Open a new query in Design View, and add tblContacts and tblSales.
If you only add the tblContacts table, you will see both sellers and buyers. When you add both tables, the query only displays buyers because tblContacts.ContactID is linked to tblSales.Buyer; the query only shows contacts who appear as a buyer in tblSales—thus eliminating the sellers.
2. Add LastName, FirstName, and State from tblContacts.
3. Click in the Criteria: cell of State.
4. Type Not NY in the cell.
Access automatically places quotation marks around NY if you don’t do so before you leave the field. You can also use <> instead of the word Not as in Figure 5-7. The query should look similar to the one shown in Figure 5-7. The query selects all records except those for buyers who live in the State of New York.
You can use the <> operator instead of Not in Step 4 of the previous instructions to exclude New York (NY). The result is the same with either operator. These two operators are interchangeable except with the use of the keyword Is. You cannot say Is <> Null. Rather, you must say Not Is Null or more accurately Is Not Null.
Figure 5-7
Using the Not operator in criteria. Entering Not NY in the State field displays all records except those where the state is NY (New York).
Entering numeric (Number, Currency, or Counter) criteria
You use numeric criteria with Number, Currency, or Counter data-type fields. You simply enter the numbers and the decimal symbol—if required—following the mathematical or comparison operator. For example, you may want to see all sales where the vehicle price was under $10,000. To create a query like this, follow these steps:
1. Open a new query in Design View, and add tblSalesLineItems and tblProducts.
2. Add Price from tblSalesLineItems, and Description and Category from tblProducts.
3. Click in the Sort: cell for Price.
4. Select Ascending from the drop-down list.
5. Click in the Criteria: cell for Price.
6. Type <10000 in the cell.
When you follow these steps, your query looks similar to the query shown in Figure 5-8. When working with numeric data, Access doesn’t enclose the expression with quotes, as it does with string criteria.
Figure 5-8
Criteria set for price of vehicles. Here the criteria is less than (<)10000.
Running this query should show 24 records under $10,000 sorted by price from $700 to $7,800.
Numeric fields are generally compared to a value string that uses comparison operators, such as less than (<), greater than (>), or equal to (=). If you want to specify a comparison other than equal, you must enter the operator as well as the value. Remember that Access defaults to equal when an operator is not specified in criteria. That is why you needed to specify less than (<) 10000 in the previous example query for vehicles under $10,000.
Working with Currency and Counter data in a query is exactly the same as working with Numeric data; you simply specify an operator and a numeric value.
Entering Yes/No (logic) criteria
Yes/No criteria are used with Yes/No type fields. The example data that you supply in the criteria can be for only Yes or No states. You can also use the Not and the <> operators to signify the opposite, but the Yes/No data also has a Null state that you may want to check for. Access recognizes several forms of Yes and No. Table 5-8 lists all the available positive and negative values.
Thus, instead of typing Yes, you can type any of these in the Criteria: cell: On, True, Not No, <> No, <No, or -1.
A Yes/No field can have three states: Yes, No, and Null. Null only occurs when no default value was set in a table and the value has not yet been entered. Checking for “Is Null” displays only records with no value, and checking for “Is Not Null” always displays all Yes or No records. After a Yes/No field check box is checked (or checked and then deselected), it can never be Null. It must be either Yes or No (–1 or 0).
Entering a criterion for an OLE object
You can even specify a criterion for OLE objects: Is Null or Is Not Null. For example, suppose you don’t have pictures for all the vehicles and you want to view only those records that have a picture of the vehicle—that is, those in which the picture is not null. You specify the Is Not Null criterion for the Picture field of tblProducts. After you do this, Access limits the records to those that have a picture in them.
Although Is Not Null is the correct syntax, you can also type Not Null and Access supplies the Is operator for you.
Entering Multiple Criteria in One Field
In previous sections of this chapter, you worked with single-condition criteria on a single field. As you learned in those sections, you can specify single-condition criteria for any field type. In this section, you work with multiple criteria based on a single field. For example, you may be interested in seeing all records in which the buyer comes from either New York, New Jersey, or Pennsylvania. Or perhaps you want to view the records of all the vehicles that were sold during the first quarter of the year 2007.
The QBE pane has the flexibility to solve these types of problems. You can specify criteria for several fields in a select query. Using multiple criteria, for example, you can determine which customers are from New York or New Jersey (“NY” or “NJ”) or which vehicles were sold for the past 90 days (Between Date() And Date() - 90).
You use the And and Or operators to specify several criteria for one field.
Understanding an Or operation
You use an Or operation in queries when you want a field to meet either of two conditions. For example, you may want to see all the records where the customer lives in either NY or NJ. In other words, you want to see all records where a customer lives in New York, in New Jersey, or both. The general formula for this operation is:
[State] = “NY” Or [State] = “NJ”
If either side of this formula is True, the resulting answer is also True. To clarify this point, consider these conditions:
• Customer 1 lives in NY—the formula is True.
• Customer 2 lives in NJ—the formula is True.
• Customer 3 lives in NY and NJ—the formula is True.
• Customer 4 lives in CT—the formula is False.
Specifying multiple values for a field using the Or operator
The Or operator is used to specify multiple values for a field. For example, you use the Or operator if you want to see all records of buyers who live in CT or NJ or NY. To do this, follow these steps:
1. Open a new query in Design View, and add tblContacts and tblSales.
2. Add FirstName, LastName, and State from tblContacts and SalesDate from tblSales.
3. Click in the Sort: cell of State.
4. Select Ascending from the drop-down list.
5. Click in the Criteria: cell of State.
6. Type CT Or NJ Or NY in the cell.
Your QBE pane should resemble the one shown in Figure 5-9. Access automatically places quotation marks around your example data—CT, NJ, and NY.
Figure 5-9
Using the Or operator. Notice the two Or operators under the State field—CT Or NJ Or NY.
Using the Or: cell of the QBE pane
Besides using the literal Or operator in a single statement on the Criteria row under the State field, you can supply individual criteria for the field on separate rows of the QBE pane. To do this, enter the first criterion example in the Criteria: cell of the field. Then enter the second criterion example in the Or: cell of the same field. Enter the next criterion in the cell directly beneath the Or: example; and continue entering examples vertically down the column. This is equivalent to typing the Or operator between examples. Using the example in which you queried for NJ, NY, or CT, change your QBE pane to look like the one shown in Figure 5-10. Notice that each state abbreviation is on a separate row in the QBE pane.
Figure 5-10
Using the Or: cell of the QBE pane. You can place each bit of example data on its own row in the Or: cells.
Access allows up to nine Or: cells for each field. If you need to specify more Or conditions, use the Or operator between conditions (for example: CT Or NJ Or NY Or PA).
Using a list of values with the In operator
Another method for specifying multiple values of a single field is using the In operator. The In operator finds a value that is one of a list of values. For example, type the expression IN(CT, NJ, NY) under the State field in the query used in Figure 5-10. The list of values in the parentheses becomes an example criterion. Your query should resemble the query shown in Figure 5-11.
In this example, quotation marks have been automatically added by Access around CT, NJ, and NY.
When you work with the In operator, each value (example data) must be separated from the others by a comma.
Figure 5-11
Using the In operator to find all records for buyer state being either CT, NJ, or NY.
Understanding an And query
You use the And operator in queries when you want a field to meet two or more conditions that you specify. For example, you may want to see records of buyers that have purchased vehicles between October 1, 2007, and March 31, 2008. In other words, the sale had to have occurred during the last quarter of the year 2007 and first quarter of 2008. The general formula for this example is (parentheses are included in this example for clarity):
[SaleDate] >= 10/1/2007 And [SaleDate] <= 3/31/2008
Unlike the Or operation (which has several conditions under which it is True), the And operation is True only when both sides of the formula are True. To clarify use of the And operator, consider these conditions:
• SaleDate (9/22/2007) is not greater than 10/01/2007, but it is less than 3/31/2008—the result is False.
• SaleDate (4/11/2008) is greater than 10/01/2007, but it is not less than 3/31/2008—the result is False.
• SaleDate (11/22/2007) is greater than 10/01/2007, and it is less than 3/31/2008—the result is True.
Both sides of the operation must be True for the And operation to be True.
Specifying a range using the And operator
The And operator is frequently used in fields that have Numeric or Date/Time data types. It is seldom used with Text data types, although it can be. For example, you may be interested in viewing all buyers whose names start with the letters d, e, or f. The And operator can be used here (> “Cz” And <”G”), although the Like operator is better (Like “[DEF]*”). Using an And operator with a single field sets a range of acceptable values in the field. Therefore, the key purpose of an And operator in a single field is to define a range of records to be viewed. For example, you can use the And operator to create a range criterion to display all buyers who have purchased vehicles between October 1, 2007, and March 31, 2008, inclusively. To create this query, follow these steps:
1. Create a new query using tblContacts and tblSales.
2. Add FirstName and LastName from tblContacts and SaleDate from tblSales.
3. Click in the Criteria: cell of SaleDate.
4. Type >= 10/1/2007 And <= 3/31/2008 in the cell.
The query should resemble the one shown in Figure 5-12.
Figure 5-12
Using the And operator with numeric fields. Notice that this query shows all records for sales during the last quarter of 2007 and the first quarter of 2008.
Using the Between...And operator
You can request a range of records based on criteria in a single field by using another method—the Between...And operator. With the Between...And operator, you can find records that meet a range of values—for example, all sales where the value of the vehicle was between $10,000 and $20,000. Using the example of sales between October 1, 2007, and March 31, 2008, create the query using the Between...And operator, as shown in Figure 5-13.
Figure 5-13
Using the Between...And operator. The results are the same as the query in Figure 5-12.
When you use the Between...And operator, the values entered in the Criteria field (in this example, 10/1/2007 and 3/31/2008) are (if they match) included in the results.
Searching for Null data
A field may have no contents for several reasons: For example, perhaps the value wasn’t known at the time of data entry, or the person who did the data entry simply forgot to enter the information, or the field’s information was removed. Access does nothing with this field; it simply remains an empty field. (A field is said to be null when it’s empty.)
Logically, a Null is neither True nor False. A Null field is not equivalent to all spaces or to zero. A Null field simply has no value.
Access lets you work with Null value fields by means of two special operators:
Is Null
Is Not Null
You use these operators to limit criteria based on Null values of a field. In the “Entering a criterion for an OLE object” section, earlier in this chapter, you learned that a Null value can be used to query for vehicles having a picture on file. In the next example, you look for buyers that don’t have the OrigCustDate field filled in. To create this query, follow these steps:
1. Create a new query using tblContacts and tblSales.
2. Add OrigCustDate, FirstName, and LastName from tblContacts, and SaleDate from tblSales.
3. Click in the Criteria: cell of OrigCustDate.
4. Type Is Null in the cell.
Your query should look like the query shown in Figure 5-14. Select the Datasheet View command to see the records that don’t have a value in the OrigCustDate field. If you add a record to tblContacts and don’t enter a value in this field, that record shows in this query’s results since OrigCustDate contains a Null value.
Figure 5-14
If the table has records with the OrigCustDate field missing a value (the user clicked past the field), they’ll be shown when you click Datasheet View command.
When using the Is Null and Is Not Null operators, you can enter Null or Not Null and Access automatically adds the Is to the Criteria field.
Entering Criteria in Multiple Fields
Previously in this chapter, you worked with single and multiple criteria specified in single fields. In this section, you work with criteria across several fields. When you want to limit the records based on several field conditions, you do so by setting criteria in each of the fields that will be used for the scope. Suppose you want to search for all sales of cars in Kansas (KS). Or, suppose you want to search for SUVs in Massachusetts or Connecticut. Or, suppose you want to search for all SUVs in Massachusetts or minivans in Connecticut. Each of these queries requires placing criteria in multiple fields and on multiple lines.
Using And and Or across fields in a query
To use the And operator and the Or operator across fields, place your example or pattern data in the Criteria: cells (for the And operator) and the Or: cells of one field relative to the placement in another field. When you want to use And between two fields, you place the example or pattern data across the same row in the QBE pane. When you want to use Or between two fields, you place the example or pattern data on different rows in the QBE pane. Figure 5-15 shows the QBE pane and a conceptual representation of this placement.
Figure 5-15
The QBE pane with And/Or criteria between fields using the Criteria: and or: rows
Figure 5-15 shows that if the only criteria fields present were Ex1, Ex2, and Ex3 (with Ex4 and Ex5 removed), all three would be And-ing between the fields. If only the criteria fields Ex4 and Ex5 were present (with Ex1, Ex2, and Ex3 removed), the two would be Or-ing between fields. As it is, the expression for this example is (Ex1 And Ex2 And Ex3) Or Ex4 Or Ex5. Therefore, this query displays a record if a value matches any of these criteria:
Ex1 And Ex2 And Ex3 (all must be True) or
Ex4 (this can be True and either/both of the other two lines can be False) or
Ex5 (this can be True and either/both of the other two lines can be False)
As long as one of these three criteria is True, the record appears in the query’s results.
Specifying And criteria across fields of a query
The most common type of condition operator between fields is the And operator. You use the And operator to limit records on the basis of several field conditions. For example, you may want to view only the records of buyers who live in the state of Massachusetts and bought Chevys. To create this query, follow these steps:
1. Create a new query using tblContacts, tblSales, tblSalesLineItems, and tblProducts.
2. Add FirstName, LastName, and State from tblContacts and Description from tblProducts.
3. Click the Criteria: cell of State.
4. Type MA in the cell.
5. Click the Criteria: cell for Description.
6. Type Like *chevy* in the cell.
Your query should look like the query shown in Figure 5-16. Notice that both example data are in the same row. If you look at the datasheet, you will see seven records that match the criteria one truck and six cars.
Figure 5-16
An And operator performing a Boolean operation based on two fields—MA in State and Like *chevy* in Description.
Because you placed data for both criteria on the same row, Access interprets this as an And operation—where both conditions must be True. If you click on the Datasheet View command, you see that you only have seven records in the query’s results.
Specifying Or criteria across fields of a query
Although the Or operator isn’t used across fields as commonly as the And operator, occasionally Or is very useful. For example, you may want to see records of any vehicles bought by contacts in Connecticut or you may want to see records on SUVs, regardless of the state they live in. To create this query, follow these steps:
1. Use the query from the previous example, emptying the two criteria cells first.
2. Add Category from tblProducts.
3. Click the Criteria: cell of State.
4. Type CT in the cell.
5. Click in the Or: cell for Category (one line below the CT example).
6. Type SUV in the cell.
Your query should resemble the query shown in Figure 5-17. Notice that the criteria entered are not in the same row of the QBE pane for both fields. When you place the criterion for one field on a different line from the criterion for another field, Access interprets this as an Or between the fields. If you click on the Datasheet View command, you see that you now have 28 records in the query’s results. You’re seeing records where the State is “CT” or the Category is “SUV.”
Figure 5-17
Using the Or operator between fields. Either condition must be True—either from the state of CT or the category of vehicle is SUV.
Using And and Or together in different fields
After you’ve worked with And and Or separately, you’re ready to create a query using And and Or in different fields. In the next example, you want to display information for all buyers of SUVs in Connecticut and all buyers of trucks in New York. To create this query, follow these steps:
1. Use the query from the previous example, emptying the two criteria cells first.
2. Click the Criteria: cell of State.
3. Type CT in the cell.
4. Click the Or: cell of State.
5. Type NY in the cell.
6. Click the Criteria: cell for Category.
7. Type SUV in the cell.
8. Click the Or: cell for Category.
9. Type Trucks in the cell.
Figure 5-18 shows how the query should look. Notice that CT and SUV are in the same row; NY and Trucks are in another row. This query represents two Ands across fields, with an Or in each field.
Figure 5-18
Using Ands and Ors across fields to select all SUVs for buyers that live in CT or all trucks whose buyers live in NY.
Clicking on the Datasheet View command displays eight records—four SUV records for CT and four truck records for NY.
A complex query on different lines
Suppose you want to view all records of Chevys that were bought in the first six months of 2007 where the buyer lives in Massachusetts, or any vehicle from buyers in California. In this example, you use three fields for setting criteria: tblContacts.State, tblSales.SaleDate, and tblProducts.Description. Here’s the expression for setting these criteria:
((tblSales.SaleDate Between #1/1/2007# And #6/30/2007#) And (tblProducts.Description = Like “*Chevy*” ) And (tblContacts.State = “MA”)) OR (tblContacts.State = “CA”)
You can display this data by creating the query shown in Figure 5-19.
Figure 5-19
Using multiple Ands and Ors across fields. This is a rather complex Select query.
You can enter the date 1/1/07 instead of 1/1/2007, and Access processes the query exactly the same. All Microsoft Office products process two-digit years from 00 to 30 as 2000 to 2030, while all two-digit years between 31 and 99 are processed as 1931 to 1999.
Creating a New Calculated Field in a Query
Fields in a query are not limited to the fields from the tables in your database. You can also create calculated fields to use in a query. For example, you can create a calculated field named Discount Amount that displays the result of multiplying the value of the discount percent (DiscountPercent) times the price (Price) in the tblSalesLineItem table.
To create this calculated field, follow these steps:
1. Create a new query using tblContacts, tblSales, tblSalesLineItems, and tblProducts.
2. Add FirstName and LastName from tblContacts, and Price and DiscountPercent from tblSalesLineItems.
3. Click the first empty Field: cell.
4. Type the following DiscountAmt: tblSalesLineItems.Price * tblSalesLineItems .DiscountPercent and click in another cell.
Your query should look like the one shown in Figure 5-20. The name of the calculated field is now DiscountAmt. If you didn’t type the name in Step 4 above, Access automatically places Expr1: as the name before the calculation. Notice that the DiscountAmt expression does not completely show in Figure 5-20; however, it should match the expression entered in Step 4 above.
Figure 5-20
A calculated field, Discount Amount, was created by multiplying two fields from tblSalesLineItems.
A calculated field has to have a name (supplied either by the user or by Access). The name—which appears before the colon (:)—is needed as a heading for the datasheet column and as a reference to the field in a form, report, or another query. If you don’t give a calculated field a name, Access will give it one for you (e.g., Expr1, Expr2, etc.).
To see the entire contents of the field cell, drag the field until it is all visible or press the Shift+F2 keys to open the Zoom window.
Notice that the general format for creating a calculated field is as follows:
CalculatedFieldName: Expression to build calculated field
Summary
In this chapter, you learned how to use various operators to create expressions in Access. You used mathematical operations to perform arithmetic calculations, relational operators to compare values, string operators to concatenate and match text patterns using wildcards, and Boolean operators to perform logical operations. You also used Is, Not, and Between...And operators.
You implemented these operators in queries to see them in action. You created simple and complex criteria, as well as calculated fields by creating expressions using the various types of operators. You learned the difference between using And and Or in your queries and how to set the QBE pane to get the desired results.