Limiting Data

3 4

You’re not restricted to evaluating expressions to summarize or otherwise analyze your data. You can also limit the results of your query by specifying criteria expressions. In other words, your query will return only those records that satisfy some condition.

The Criteria cell in the design grid accepts expressions that limit the records returned by the query. You might want to see only customers from a specific state or country. Or perhaps you want to view orders that were taken on a certain day or during a specific week. The reasons for limiting the results of a query are almost limitless.

Suppose you want to find all employees with the last name Fuller. One way to do this in Access is to create a new query in Design view, add the Employees table to the query, add the LastName field to the design grid, and type Fuller in the Criteria cell.

The Jet SQL statement that’s created is shown here. Notice that it includes an expression that indicates that the LastName field must equal the string " Fuller".

 SELECT Employees.LastName FROM Employees  WHERE (((Employees.LastName)="Fuller")); 

tip - Specify criteria designators

---

When you enter a value in the Criteria cell of a query’s design grid, enclose it within double quotation marks if it’s a string (text) value or pound signs (#) if it’s a date. Numeric values don’t need a designator. Access will attempt to supply the appropriate designators when you tab away from the Criteria cell after typing in a value or expression, but check to be sure that the designators are correct.

An expression is a statement that contains an operator and at least one operand. (Entering "Fuller" in the Criteria cell satisfies this condition; even though you don’t explicitly include the equal sign (=) operator, Access just assumes it.) The elements common to expressions are described here:

• Operators. The symbols that represent mathematical, comparison, logical, and concatenation tasks. (Table 9-1 contains a list of operators.)
• Operands. The values you want the expression to evaluate. Operands come from more than one source, including literal values, identifiers, and functions. The previous example consisted of just one literal, the string "Fuller". Some expressions refer to Access objects by name. When you refer to a field, a form, or a report by name, you’re using an identifier. Functions that return a value can also be used as operands in an expression.

Table 9-1. Some operators that can be used in expressions

Arithmetic		Comparison		Logical
+	Addition	=	Equals	Or	Meets any one condition
-	Subtraction	>	Greater than	And	Meets all conditions
*	Multiplication	<	Less than	Not	Negates argument
/	Division	>=	Greater than or equal to	Between	Falls between two extremes and includes the extremes
^	Exponentiation	<=	Less than or equal to
		<>	Not equal to
		Is	SQL equivalent to equal sign
		Like	String matches a pattern

You can limit records using a number of methods, but most of the time a query that limits data will fall into one of three categories, as follows:

• Restricted by one condition (simplest)
• Restricted by more than one field
• Restricted by one field or another

These three categories don’t require much explanation, but creating queries within each category does. Remember that all three categories of queries are created by either a field entry or the results of an expression.

Restricting by One Condition

Perhaps the simplest restrictive query uses a field entry as its criteria. In other words, the criteria expression equals an actual entry, such as the name of a customer or a particular ZIP code. A query of this type can return one or many records, but all the records will have something in common—the contents of one field. The query shown in Figure 9-13 will return only those records in which the UnitPrice entry equals $10.

Figure 9-13. The expression in the Criteria row limits the query.

The results of this query are shown in Figure 9-14.

Figure 9-14. The previous query returns only those records in which the UnitPrice field equals $10.

At first, these queries seem similar to the Filter By Selection results available in Datasheet view and Form view, but a query isn’t limited by just an actual field entry, which is how the Filter By Selection feature limits its results. Also, you’re not limited to the equals comparison operator. For example, the following expression would include all the records in which the UnitPrice field does not equal $10:

 <>10 

Similarly, you could look for a range of unit price values using the Between and And operators. For example, the following expression would return the records shown in Figure 9-15:

 Between 8 And 10 

Figure 9-15. Here the results are limited to records that have a unit price between $8 and $10 (inclusive of both values).

note

---

The Between operator is inclusive, so the results in Figure 9-15 include records that have a unit price of $8 as well as $10.

Restricting by More than One Field

You can express multiple conditions in two ways: You can use the Criteria cells to enter single conditions for each field, or you can enter all the conditions in one Criteria cell using the And operator. For instance, both queries shown in Figure 9-16 will return only those records in which the UnitsInStock value is less than 10 and the UnitsOnOrder value equals 0.

Figure 9-16. These two queries express the same And condition in different ways.

You can prove that the queries are the same by viewing the Jet SQL statement in SQL view. The first query’s SQL statement is shown here:

 SELECT Products.ProductID, Products.ProductsName,  Products.UnitsInStock, Products.UnitsOnOrder FROM Products WHERE (((Products.UnitsInStock)<10) And ((Products.UnitsOnOrder)=0)); 

The second query’s SQL statement is shown here:

 SELECT Products.ProductID, Products.ProductName,  Products.UnitsInStock, Products.UnitsOnOrder FROM Products WHERE (((Products.UnitsInStock)<10 And [UnitsOnOrder]=0)); 

Although the locations of the parentheses are different in the WHERE clauses, the statements are equivalent.

InsideOut

SQL statements generated by Access often contain extra sets of parentheses (as in the preceding SQL statement). They also typically contain the table names in front of the field name. If you need to copy a SQL statement from SQL view for use in Microsoft Visual Basic for Applications (VBA) code, you can usually strip out the extra sets of parentheses and the table names (including the period) in front of each field name—they generally aren’t necessary for the statement to execute properly.

Brackets are needed only for field names that contain spaces; they can be deleted for field names that don’t have spaces. If only one data source is used in the query, you can delete the table or query name (and the following period) before field names too. For example, the preceding SQL statement could be condensed to the following statement:

 SELECT ProductID, ProductName, UnitsInStock, UnitsOnOrder FROM Products WHERE UnitsInStock<10 And UnitsOnOrder=0; 

To do the cleanup on a large query, copy the SQL statement to Word, where you can use Word’s find and replace utility, and then copy the edited statement back into the SQL window.

Restricting by One Field or Another

Records won’t always need to meet all conditions. Sometimes a record needs to meet only one condition even if there are several conditions. The example from the previous section returns those records in which the UnitsInStock value is less than $10 and the UnitsOnOrder field equals 0. This time, let’s return all records that meet one or the other condition rather than both by using the Or cell or the Or operator.

Both queries shown in Figure 9-17 return the records shown in Figure 9-18.

Figure 9-17. These two queries express the same Or condition in different ways.

Figure 9-18. Both Or queries return the same records.

At first glance, you might think a mistake’s been made, but the results are correct. (The 0 expression has been moved to the Or cell; the And operator has been changed to Or.) Only one condition must be met. If you review the records carefully, you’ll see that all records whose UnitsInStock value is more than 10 have a UnitsOnOrder value of 0 and all records whose UnitsOnOrder value doesn’t equal 0 have a UnitsInStock value of less than 10.

Again, Jet SQL statements prove that the queries are equivalent. The Or cell Jet SQL statement is shown here:

 SELECT Products.ProductID, Products.ProductName,  Products.UnitsInStock, Products.UnitsOnOrder FROM Products WHERE (((Products.UnitsInStock)<10)) Or (((Products.UnitsOnOrder)=0)); 

The Or operator criteria Jet SQL statement is shown here:

 SELECT Products.ProductID, Products.ProductName,  Products.UnitsInStock, Products.UnitsOnOrder FROM Products WHERE (((Products.UnitsInStock)<10 Or [UnitsOnOrder]=0)); 

As with the And examples in the previous section, the only difference is the parentheses. In some situations, the organization of the parentheses could make a huge difference, but that’s not the case with the And and Or operators.

Understanding the Or Operator

Most of the time, returning all the records that meet all conditions (using the And operator) doesn’t produce any surprises. You want any records that meet all the conditions, regardless of the number of conditions. On the other hand, returning records that meet any of the conditions can be a bit trickier. Or criteria don’t always return the expected results, so you must be careful when designing queries that use Or.

The best way to understand how Or criteria work is to look at a couple of examples—one that works and one that doesn’t. First review the query shown in Figure 9-19.

Figure 9-19. The record must match either the expression in the Criteria cell or the condition in the Or cell.

This query returns the records shown in Figure 9-20.

Figure 9-20. This query returns only those records that meet one or the other criterion.

The query has the following Jet SQL statement:

 SELECT Customers.CustomerID FROM Customers WHERE (((Customers.CustomerID)="BERGS")) Or  (((Customers.CustomerID)="BSBEV")); 

To be included in the results, a record needs to meet only one of the specified conditions. Specifically, the contents of the CustomerID field must equal "BERGS" or "BSBEV". As Access runs the query, it checks the CustomerID field of each record. If the CustomerIDfield for an entry equals "BERGS", the Criteria expression is true. If the CustomerID field for an entry equals "BSBEV", the Or expression is true. If either expression is true, Access includes that record in the results. If both (or all) expressions are false, the record isn’t included.

The first four entries in the Customers table aren’t included because neither condition is met. Both expressions are false and the records are excluded from the results. When Access evaluates the fifth record, the Criteria expression is true because the CustomerID field equals "BERGS". The Or expression is false, but that doesn’t matter. Only one expression needs to be true for the record to be included. The next five records fail to meet either condition, and Access excludes those records. The entry for the eleventh record doesn’t meet the Criteria expression’s condition. However, it does meet the Or expression’s condition, and Access includes the record in the results.

Now let’s muddy the water a bit by adding the Not operator, as shown in Figure 9-21.

Figure 9-21. The Not operator has been added to both expressions.

You might expect this query to return all records except those with entries that match "BERGS" or "BSBEV". Unfortunately, this query doesn’t work as expected. It returns all records, as shown in Figure 9-22.

Figure 9-22. This exclusive query doesn’t exclude any records.

The problem isn’t Access or the query; the problem is your expectation. Don’t forget that the expression needs to meet only one condition to be included in the results. Consider the first four records: The CustomerID entry doesn’t match "BERGS" or "BSBEV", so both conditions are false. The Not operator then negates this value to true. Consequently, all four records are included in the results. (Although only one expression needs to be true, both are true in the case of each of the first four records.)

Now consider the fifth entry, which has a CustomerID equal to "BERGS". When first evaluated, the Criteria expression is true. However, the Not operator negates that value to false. So far, so good, right? The problem comes when the Or expression returns false. The Not operator then negates it to true. Because one of the expressions is true, Access includes the record in the results. The same situation occurs for the eleventh record, which has a CustomerID equal to "BSBEV". The only difference is that the Or expression is false and the Criteria expression is True, once negated by the Not operator.

Take a look at the query’s equivalent Jet SQL statement. Each side of the expression includes the Not operator, which is the reason for the unexpected results.

 SELECT Customers.CustomerID FROM Customers WHERE ((Not (Customers.CustomerID)="BERGS")) Or  

 ((Not (Customers.CustomerID)="BSBEV")); 

The solution is to include both conditions in the same expression, as shown in Figure 9-23.

Figure 9-23. Combine both conditions in the same expression.

As shown in Figure 9-24, this query returns the expected records. The difference is that the Or operator evaluates both conditions before the Not operator has a chance to negate anything.

Figure 9-24. The combined Not expression works as expected, and the results don’t include CustomerIDs that equal "BERGS" or "BSBEV".

Here’s the updated Jet SQL statement:

 SELECT Customers.CustomerID FROM Customers WHERE ((Not ((Customers.CustomerID)="BERGS" Or  (Customers.CustomerID)="BSBEV"))); 

Working with Wildcards

Ever play poker? If so, you know that a wildcard can significantly increase your odds of securing a better hand. The same is true when you’re searching through records for specific data. Wildcards minimize your search effort by returning records that fit a profile, rather than matching each condition exactly.

Access supports several wildcard characters; the four most commonly used wildcards are listed in Table 9-2. When searching records for matching entries, you can substitute wildcard characters for literal characters or entire blocks of literal characters. Wildcards make a search more flexible.

Table 9-2. Access wildcard characters

Character	Name	Description
*	Asterisk	Matches any character or block of characters in that position
?	Question mark	Matches any single character in the same position
[]	Brackets	Specifies a range of characters (including numeric values)
!	Exclamation point	Excludes characters from the series specified in []

tip

---

To get information about the more obscure wildcard characters not listed in Table 9-2, type wildcard in the Ask A Question box on the main Access menu, and select the "About using wildcard characters" topic.

Table 9-3 shows some search criteria that use wildcard characters and examples of fields that would match. These examples will be discussed throughout this section.

Table 9-3. Search criteria that use wildcard characters

Search criteria	Description	Examples
Like "an*"	Matches any string that begins with "an"	Ana Trujillo Antonio Moreno Ann Devon
Like "*an*"	Matches any string that contains "an", regardless of its position	Maria Anders Ana Trujillo Antonio Moreno
Like "B?l*"	Matches any string that begins with a b and has an l as its third character	Bólido Comidas preparadas
Like "[A-C]*"	Matches any string that begins with a, b, or c	Ana Trujillo Bernardo Batista Christina Berglund
Like "[A-C,F-H,K-P]*"	Matches any string that begins with a, b, c, f, g, h, k, l, m, n, o, or p	Ana Trujillo Bernardo Batista Christina Berglund Frederique Citeaux
Like "[!A-C,F-H,K]*"	Matches any string that doesn’t begin with a, b, c, f, g, h, or k	Elizabeth Lincoln Laurence Lebihan Maria Anders
Like "c[a-c]*"	Matches any string that begins with c and contains a, b, or c as the second character	Carine Schmitt Carlos Hernandez
Like "c[!a-c]*"	Matches any string that begins with c and doesn’t contain a, b, or c as the second character	Christina Berglund

Consider the search criterion, which uses the asterisk (*), as shown in Figure 9-25.

Figure 9-25. Use the * wildcard to match any character.

The simple expression "an" would find only those entries with a ContactName that equaled "an". If you append the * wildcard to the end of the search string, the query matches any entry that begins with "an", as shown in Figure 9-26.

Figure 9-26. Using the "an*" search string, all entries that begin with "an" are returned.

note

---

When you add a wildcard character to a search string, Access automatically inserts the Like operator. A search for literal characters doesn’t require the Like operator.

If you use the "*an*" search string, any entry that contains the string "an" regardless of its position will match. (The "an*" example matched only entries beginning with the string "an".)

As you can see, the position of the * wildcard character is as important as the wildcard itself. The position is even more relevant when you use the question mark (?) as a wildcard. You can replace any character within a search string to represent that one character in only that position. For instance, suppose you don’t know how to generate the ó character in Bólido Comidas preparadas (a customer listed in the Northwind Customers table). You could try the following search criteria:

 "Bolido Comidas preparadas" 

This query would return an empty result set, however, because the o and ó are not equivalent.

The simplest solution might be to use the "B?l*" search criteria. The ? wildcard will match any single character between the letter B and the letter l. In addition, the * wildcard matches any characters that follow the string B?l.

You might want a bit more flexibility when you’re matching a range or series of characters. Perhaps you want to find all the customers whose names begin with the letters a, b, and c. In this case, you’d employ the brackets ([]) and the * wildcard—for example, "[A-C]*".

A series search can include more than one series. For example, you could use the following search expression to match all companies whose names begin with the letters A, B, C, F, G, H, K, L, M, N, O, and P:

 Like "[A-C,F-H,K-P]*" 

Each component in the brackets doesn’t have to be a series, however. A component can reference a single character. For example, the following expression would match companies whose names begin with the letters A, B, C, F, G, H, or K:

 Like "[A-C,F-H,K]*" 

Wildcards certainly do add a lot of possibilities, and the exclamation point (!) is no exception. This wildcard allows you to exclude characters within a series. Suppose you want to return all the companies whose names begin with letters other than A, B, C, F, G, H, or K. In that case, you’d use the following search expression:

 Like "[!A-C,F-H,K]*" 

You can also add a literal character to a series. For instance, the following search expression matches all the names that begin with the letters Ca through Cc:

 Like "c[a-c]*" 

To return names that begin with C but don’t have A, B, or C as the second character, you could add the exclamation point to the search expression, as shown here:

 Like "c[!a-c]*" 

Chapter 11, "Working with Advanced Queries," discusses including wildcard characters in a parameter query.