Types of operators

Operators can be grouped into the following types:

• Mathematical
• Comparison
• String
• Boolean (logical)
• Miscellaneous

[TaxAmt]+[Price]

[Price] - ([Price] * [DiscountPercent])

[Quantity] * [Price]

1000000 / 212

5 Mod 2 = 1
4 Mod 2 = 0

"Man" > "Woman"

[FirstName] & [LastName]

[FirstName] & " " & [LastName]

[HouseNumber] & " " & [StreetName]

"1600 Pennsylvania Avenue N.W."

"This report was printed " & Now() & " by " & [OperatorName]

This report was printed 3/21/12 4:45:40 PM by Jim Rosengren

[FirstName] + " " + [LastName]

MyVariable = txtLastName & ""

MyVariable = txtLastName + ""

expression Like pattern

Expression1 And Expression2

Expression1 Or Expression2

Not [numeric|boolean] expression

expression Between value1 And value2

Expression In (value1, value2, value3, …)

In ('SUV','Trucks')

If [tblCustomers].[City] In("Seattle", "Tacoma") Then

If strCity Not In ("Pittsburgh", "Philadelphia") Then

expression Is Null

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. Access has a built-in predetermined order for mathematical, logical, and Boolean operators, known as operator precedence. Access always follows this order unless you use parentheses to override its default behavior.

Operations within parentheses are performed before operations outside parentheses. Within parentheses, Access follows the default 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 in the following sections.

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 cell or the Criteria cell of the Query by Example (QBE) pane.

Table 9.4 shows the most common operators 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 truck or an SUV
• All records that do not match a value, such as any category that is not a car

When you add criteria to a query, use the appropriate operator with an example of what you want. In Figure 9.1, the example is Cars. The operator is equal (=). Notice that the equal sign is not shown in the figure because it's the default operator for select queries.

Understanding complex criteria

You build complex query criteria using any combination of the operators shown in Table 9.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, as shown in Figure 9.2. In Figure 9.2, criteria is specified in both the State and Category columns. Within the State column, the criteria specifies “either California or Arizona,” while the additional criteria in the Category column adds “not Cars.” When combined, the criteria in the two columns limit the returned records to those where the customer state is either California or Arizona, and the product category is not cars.

However, using explicit Boolean operators is not the only way to select records based on multiple criteria. Figure 9.3 demonstrates a common Access technique using complex criteria without entering the operator keywords And/Or at all. In this example, the criteria “stacked” within a single column specifies Or. For example, in the State column, the criteria is interpreted as "CA" Or "AZ". The presence of criteria in another column in the QBE grid implies And. Therefore, the criteria in the Category column is combined with the state criteria and is interpreted as:

(State = "CA" And Category <> "Cars") Or
(State = "AZ" And Category <> "Cars")

In any case, the queries in Figures 9.2 and 9.3 are equivalent and return the same data.

One confusing aspect about the query in Figure 9.3 is that the criteria in the Category column must appear twice, once for each value in the State column. If the Category criteria appeared only once, perhaps in the same row as "AZ" in the State column, the combined criteria would be interpreted as:

(State = "AZ" and Category <> "Cars") Or (State = "CA")

You learn how to create this type of complex query in the “Entering Criteria in Multiple Fields” section, later in this chapter.

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 9.4), which Access creates from the fields and criteria placed in the QBE pane in Figure 9.3.

The SQL statement in Figure 9.4 has been slightly rearranged 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.

An expression for this query's criteria is:

(tblCustomers.State = "CA" AND tblProducts.Category <> "Cars") OR
(tblCustomers.State = "AZ" AND tblProducts.Category <> "Cars")

You must enter the category criteria (<> "Cars") for each state in the QBE pane, as shown in Figure 9.3. 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.

The And/Or operators are the most common operators when working with complex criteria. The operators consider two different expressions (one on each side of the And/Or operators) and then determine whether the expressions are true or false. Then the operators compare the results of the two expressions against each other for a logical true/false answer. For example, take the first And statement in the expression given in the preceding paragraph:

(tblCustomers.State = "CA" AND tblProducts.Category <> "Cars")

The right side of the criteria (tblProducts.Category <> "Cars") evaluates to True if the Category is anything other than Cars. The And operator compares the logical true/false from the left and right expressions to return a true/false answer.

When the result of an And/Or operation is True, the overall condition is true, and the query displays the records meeting the true condition.

Notice that the result of an And operation is true only when both sides of the expression are true, whereas the result of an Or operation is true when either side of the expression 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 fundamental difference between And/Or operators.

Using functions in select queries

When you work with queries, you might want to use built-in Access functions to display information. For example, you might want to display items such as:

• The day of the week 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.

Referencing fields in select queries

When you reference table names and field names in queries, it is a best practice to enclose names in square brackets ([]). Access actually requires brackets around any name that contain spaces or punctuation characters. An example of a field name in brackets is:

 [tblSales].[SaleDate] + 30

In this example, 30 days is added to the SaleDate field in tblSales.

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 query that returns customers who live in New York, for example, follow these steps:

1. Open a new query in Design view based on tblCustomers 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 like Figure 9.5. Notice that only one table is open and only three fields are selected. Click the Datasheet View button in the Home Ribbon's Views group to see this query's results.

   

You don't have to enter an equal sign before the literal word NY, because this is a select query. To see all states except New York, you must enter either the <> (not equal) or the Not operator before NY.

You also don't have to type quotes around NY. Access assumes that you're using a literal string NY and adds the quotes for you automatically.

Special considerations apply when data in the field contains quotation marks. For example, consider a query to find a person whose name is given as Robert “Bobby” Jones. Ideally, the contacts table would include a nickname field to capture “Bobby,” but, in the absence of a nickname field, the data entry clerk may enter the first name as Robert “Bobby,” using the quotation marks around “Bobby.”

In this case, Access sees the double-quotation characters as data, and you may want to include the quotes in the criteria. The simplest solution is to use a criteria expression such as the following:

'Robert "Bobby"'

Notice the single quotes surrounding the criteria string. Access correctly interprets the single quotes as delimiting characters, and understands that the double quotes within the single quotes are just data. You shouldn't use an expression such as the following:

"Robert 'Bobby'"

This is, of course, the opposite use of quotation marks as the previous example. In this case, Access expects to find single quotes around “Bobby” in the first name field, and no records will be returned.

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 records. Sometimes, however, you know only a part of the field contents, or you might want to see a wider range of records on the basis of a pattern.

For example, you might want to see all product information for items with “convertible” in the description. Many different makes and models may be convertibles, and there's no field where “convertible” will work by itself as the query's criteria. You'll need to use wildcards to make sure you successfully select all records containing “convertible” in the description.

Here's another example: Suppose you have a buyer who has purchased a couple of red models in the last year. You remember making a note of it in the Notes 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 Notes field in tblCustomers to find records that contain the word red.

Use the Like operator 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. In addition to ? and *, Access uses three other characters for wildcard searches. The table in the “Using Wildcards” sidebar earlier in this chapter 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. The pound sign (#) stands for a single digit (0–9) found in the position occupied by 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 operator for the list—that is, any single character that does not match any character in the list.

These wildcards can be used alone or in conjunction with each other. They can even be used multiple times within the same expression.

To create an example using the Like operator, let's suppose you want to find the customer who likes red model cars. You know that red is used in one of the Notes field in tblCustomers. To create the query, follow these steps:

1. Add tblCustomers, tblSales, tblSalesLineItems, and tblProducts to the query.
2. Add Company and Notes from tblCustomers, SalesDate from tblSales, and Description from tblProducts to the QBE pane.
3. Click the Criteria cell of the Notes field and enter * red * as the criteria. 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.

There is, however, one issue with this example. Notice that the criteria ("* red *") requires a space after the word red. This means that a record containing the following note will not be returned by this query:

1. Customer wants any model of car, as long as it's red!

Because there is no space immediately after red, this record will be missed. The proper criteria to use is:

Like "* red[ ,.!*]"

The brackets around “ ,.!*” instruct Access to select records when the Notes field ends with the word red, followed by a space or punctuation character. Obviously, there may be other characters to consider within the brackets, and you must have a good idea of the variety of data in the queried field.

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 like Figure 9.6.

After creating this query, click on the Datasheet View command to view the query's results. It should look like Figure 9.7.

If you click the Datasheet View command on the Ribbon, you see that a number of records match your query's criteria. The recordset returned by this query includes redundant information in the Company and Notes columns, but the redundancy is the result of asking for this information along with the sales and product data.

Access automatically adds the Like operator and quotation marks if you meet these conditions:

• Your expression contains no spaces.
• You use only the wildcards ?, *, or #.
• 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's just as useful in text and date fields, as the examples in Table 9.5 demonstrate. Table 9.5 shows several examples that can be used to search records in the tables of the 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 might want to see all contacts that have purchased a vehicle, but you want to exclude buyers from New York. Follow these steps to see how to specify this non-matching value:

1. Open a new query in Design view and add tblCustomers.
2. Add Company and State from tblCustomers.
3. Click in the Criteria cell of State.
4. Type <> NY in the cell. Access automatically places quotation marks around NY if you don't do so before you leave the field. The query should look like Figure 9.8. The query selects all records except those for customers who live in the state of New York.

   

Entering numeric criteria

You use numeric criteria with numeric or currency data-type fields. You simply enter the numbers and the decimal symbol—if required—following the mathematical or comparison operator (but do not use commas). For example, you might want to see all sales where the product's inventory count is less than ten:

1. Open a new query in Design view, and add tblProducts.
2. Add ProductID, Description, Make, Model, and QtyInStock from tblProducts to the QBE grid.
3. Click in the Sort cell for Make and select Ascending from the drop-down list.
4. Click in the Criteria cell for QtyInStock and enter <10 in the cell. Your query should look like Figure 9.9. When working with numeric data, Access doesn't enclose the expression with quotes, as it does with string criteria.

   

The criteria applied to numeric fields usually include 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 running a select query. That's why you needed to specify <10 in the QtyInStock column in the example shown in Figure 9.9.

Access does not surround the criteria with quotes because QtyInStock is numeric and requires no delimiter.

Entering true or false criteria

True and false criteria are used with Yes/No type fields. The example data that you supply as criteria must evaluate to true or false. 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 might want to consider. Access recognizes several forms of true and false.

Thus, instead of typing Yes, you can type any of these in the Criteria: cell: On, True, Not No, <> No, <No, or –1.

Entering OLE object criteria

You can specify criteria for OLE objects: Is Null or Is Not Null. For example, suppose you don't have pictures for all the products and you want to view only those records that have a picture—that is, those in which the picture is not null. You specify the Is Not Null criterion for the Picture field of tblProducts.

Understanding an Or operation

You use an Or operator in queries when you want a field to meet either of two conditions. For example, you might want to see all the records where the customer has an address in either New York or California. In other words, you want to see all records where a customer has addresses in NY, in CA, or both. The general expression for this operation is:

[State] = "NY" Or [State] = "CA"

If either side of this expression is true, the resulting answer is also true. To clarify this point, consider these conditions:

• Customer 1 has an address in NY: The expression is true.
• Customer 2 has an address in CA: The expression is true.
• Customer 3 has an address in NY and CA: The expression is true.
• Customer 4 has an address in CT: The expression is false.

Specifying multiple values with 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 tblCustomers and tblSales.
2. Add Company and State from tblCustomers and SalesDate from tblSales.
3. Click in the Criteria cell of State.
4. Type AZ Or CA Or NY in the cell. Your QBE pane should resemble the one shown in Figure 9.10. Access automatically places quotation marks around your example data—AZ, CA, and NY.

   

Using the Or cell of the QBE pane

Besides using the literal Or operator as a single expression on the Criteria row under the State field, you can supply individual criteria for the field vertically on separate rows of the QBE pane, as shown in Figure 9.11.

Access rearranges the design shown in Figure 9.11 when the query is saved to match the query in Figure 9.10. In fact, when you open qryFigure_9-11 in the Chapter09.accdb example database, you'll see that it is exactly the same as qryFigure_9-10 because of the way Access rearranged the criteria when qryFigure_9-11 was originally saved. When you build a query using “vertical” Or criteria, Access optimizes the SQL statement behind the query by placing all the Or criteria into a single expression.

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 from a list of values. For example, use the expression IN(AZ, CA, NY) under the State field in the query used in Figure 9.11. The list of values in the parentheses becomes an example criterion. Your query should resemble the query shown in Figure 9.12.

Access automatically adds quotation marks around AZ, CA, and NY.

Using And to specify a range

The And operator is frequently used in fields that have numeric or date/time data types. It's seldom used with text data types, although it can be this way in some situations. For example, you might be interested in viewing all buyers whose names start with the letters d, e, or f. The And operator can be used here (>="D" And <="G"), although the Like operator is better (Like "[DEF]*") because it's much easier to understand.

You use the And operator in queries when you want a field to meet two or more conditions that you specify. For example, you might want to see records of buyers who have purchased products between October 1, 2012, and March 31, 2013. In other words, the sale had to have occurred during the last quarter of the year 2012 and the first quarter of 2013. The general expression for this example is:

(SaleDate >= #10/1/2012#) And (SaleDate <= #3/31/2013#)

Unlike the Or operation (which has several conditions under which it is true), the And operation is true only when both sides of the expression are true. To clarify the use of the And operator, consider these conditions:

• SaleDate (9/22/2012) is not greater than 10/01/2012 but is less than 3/31/2013: The result is false.
• SaleDate (4/11/2013) is greater than 10/01/2012 but is not less than 3/31/2013: The result is false.
• SaleDate (11/22/2012) is greater than 10/01/2012 and less than 3/31/2013: The result is true.

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 products between October 1, 2012 and March 31, 2013, inclusively. To create this query, follow these steps:

1. Create a new query using tblCustomers and tblSales.
2. Add Company from tblCustomers and SaleDate from tblSales.
3. Click in the Criteria cell of SaleDate.
4. Type >= #10/1/2012# And <= #3/31/2013# in the cell. The query should resemble Figure 9.13.

   

Notice the pound signs (#) used to delimit the dates in the expressions on both sides of the And operator. Access recognizes pound signs as delimiters for date and time values. Without the pound signs, Access evaluates the date values as numeric expressions (10 divided by 1 divided by 2012, for example).

Using the Between…And operator

You can request a range of records using another method—the Between…And operator. With Between…And, you can find records that meet a range of values—for example, all sales where the list price of the product was $50 or $100. Using the previous example, create the query shown in Figure 9.14.

The operands for the Between…And operator are inclusive. This means that sales on 10/1/2012 and 3/31/2013 are included in the query results.

Searching for null data

A field might 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. Unless a default value is specified in the table design, the field simply remains empty. (A field is said to be null when it's truly empty.)

Logically, 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 the null state of a field. Earlier in this chapter, you learned that a null value can be used to query for products having a picture on file. In the next example, you look for buyers that don't have the Notes field filled in:

1. Create a new query using tblCustomers and tblSales.
2. Add Notes and Company from tblCustomers, and SaleDate from tblSales.
3. Enter Is Null as the criteria in the Notes field.
4. Uncheck the Show box in the Notes field.

Your query should look like Figure 9.15. Select the Datasheet View command to see the records that don't have a value in the Notes field.

You unchecked the Show box because there is no need to display the Notes field in the query results. The criteria selects only those rows where the Notes field is null, so there is, quite literally, nothing to see in the Notes field and no reason to display it in the results.

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 or more 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 criteria on different rows in the QBE pane. Figure 9.16 shows the QBE pane and a rather extreme example of this placement.

The query in Figure 9.16 displays a record if a value matches any of the following criteria:

• ModelYear = 1932 And Make = Ford And Model = Coupe (all must be true).
• Color = Green (this can be true even if either or both of the other two lines are false).
• Category = Cars (this can be true even if either or both of the other two lines are false).

As long as one of these three criteria is true, the record appears in the query's results.

Here's the core SQL statement behind the query in Figure 9.16:

SELECT ModelYear, Make, Model, Color, Category
FROM tblProducts
WHERE ((ModelYear="1932") AND (Make="Ford") AND (Model="Coupe"))
OR (Color="Green")
OR (Category="Cars")

The locations of the parentheses in this SQL statement are significant. One set of parentheses surrounds the criteria for Field1, Field2, and Field3, while parentheses surround each of the criteria applied to Field4 and Field5. This means, of course, that ModelYear, Make, and Model are applied as a group, while Color and Category are included individually.

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 might want to see records of any models bought by contacts in Connecticut or you might want to see records on truck models, regardless of the state the customers live in. To create this query, follow these steps:

1. Add tblCustomers, tblSales, tblSalesLineItems, and tblProducts to a new query.
2. Add Company and State from tblCustomers, and Description and Category from tblProducts.
3. Enter CT as the criteria for State.
4. Enter Trucks in the OR cell under Category. Your query should resemble Figure 9.17. Notice that the criteria entered are not in the same row of the QBE pane for State and Category. When you place criteria on different rows in the QBE grid, Access interprets this as an Or between the fields. This query returns customers who either live in Connecticut or have bought truck models.

   

Here's the SQL statement behind the query in Figure 9.17:

SELECT tblCustomers.Company, tblCustomers.State,
tblProducts.Description, tblProducts.Category
FROM tblProducts
INNER JOIN (tblCustomers
INNER JOIN (tblSales INNER JOIN tblSalesLineItems
ON tblSales.InvoiceNumber = tblSalesLineItems.InvoiceNumber)
ON tblCustomers.CustomerID = tblSales.CustomerID)
ON tblProducts.ProductID = tblSalesLineItems.ProductID
WHERE (tblCustomers.State="CT") OR (tblProducts.Category="Trucks")

Notice the placement of parentheses in the WHERE clause. Either condition (State = "CT" or Category="Trucks") can be true, and the record is returned by the query.

Moving “Trucks” to the same row as “CT” in the QBE grid changes the query's logic to return customers who live in Connecticut and have bought truck models. The rearranged query is shown in Figure 9.18.

Here's the SQL statement for this minor rearrangement:

SELECT tblCustomers.Company, tblCustomers.State,
tblProducts.Description, tblProducts.Category
FROM tblProducts
INNER JOIN (tblCustomers
INNER JOIN (tblSales INNER JOIN tblSalesLineItems
ON tblSales.InvoiceNumber = tblSalesLineItems.InvoiceNumber)
ON tblCustomers.CustomerID = tblSales.CustomerID)
ON tblProducts.ProductID = tblSalesLineItems.ProductID
WHERE (tblCustomers.State="CT") AND (tblProducts.Category="Trucks")

The difference is significant because the rearrangement is considerably more restrictive when returning records. Only one record is returned by qryFigure_5-18, while qryFigure5-17 returns 17 rows.

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, the query displays records for all buyers of motorcycle models in Connecticut and buyers of truck models in New York:

1. Use the query from the previous example, emptying the two criteria cells first.
2. Enter CT in the Criteria row in the State column.
3. Enter NY in the OR row under CT in QBE grid.
4. Type Cars as criteria in the Category field.
5. Enter Trucks under Cars in the Category field. Figure 9.19 shows how the query should look. Notice that CT and Cars 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.

   

The important thing to notice about this query is that Access returns, essentially, two sets of data: car model owners in Connecticut and truck model owners in New York. All other customers and model combinations are ignored.

A complex query on different lines

Suppose you want to view all records of Chevy models bought in the first six months of 2012 where the buyer lives in Massachusetts or any type of vehicle from buyers in California, regardless of when they were bought. In this example, you use two fields for setting criteria: tblCustomers.State, tblProducts.Description, and tblSales.SaleDate. Here's the expression for setting these criteria:

((tblSales.SaleDate Between #1/1/2012# And #6/30/2012#) And
(tblProducts.Description = Like "*Chev*" ) And
(tblCustomers.State = "MA")) OR (tblCustomers.State = "CA")

The query design is shown in Figure 9.20.