One advantage of using queries is that they allow you to find data easily in multiple related tables. Queries are also useful, however, for sifting through the data in a single table. All the techniques you use for working with a single table apply equally to more complex multiple-table queries. This chapter covers the basics about building queries to select data from a single table. The next chapter shows you how to build more complex queries with multiple tables, totals, parameters, and more.
The easiest way to start building a query on a single table is to click the Query Design button in the Queries group on the Create tab (see Figure 9-1). Access 2010 displays the Show Table dialog box on top of the query design grid, as shown in Figure 9-3.
Figure 9-3. The Show Table dialog box allows you to select one or more tables or queries to build a new query.
Select tblContacts on the Tables tab of the Show Table dialog box and then click Add to place tblContacts in the upper part of the Query window. Click Close in the Show Table dialog box to view the window shown in Figure 9-4.
Figure 9-4. The Query window in Design view for a new query on tblContacts shows the table with its list of fields in the top part of the window.
As mentioned earlier, the Query window in Design view has two main parts. In the upper part, you find field lists with the fields for the tables or queries you chose for this query. The lower part of the window is the design grid, in which you do all the design work. Each column in the grid represents one field that you’ll work with in this query. As you’ll see later, a field can be a simple field from one of the tables or a calculated field based on several fields in the tables.
You use the first row of the design grid to select fields—the fields you want in the resulting recordset, the fields you want to sort by, and the fields you want to test for values. As you’ll learn later, you can also generate custom field names (for display in the resulting recordset), and you can use complex expressions or calculations to generate a calculated field.
The second row shows you the name of the table from which you selected a field. If you don’t see this row, you can display it by clicking Table Names in the Show/Hide group on the Design tab below Query Tools. This isn’t too important when building a query on a single table, but you’ll learn later that this row provides valuable information when building a query that fetches data from more than one table or query.
In the Sort row, you can specify whether Access 2010 should sort the selected or calculated field in ascending or descending order. In the Show row, you can use the check boxes to indicate the fields that will be included in the recordset. By default, Access 2010 includes all the fields you place in the design grid. Sometimes you’ll want to include a field in the query to allow you to select the records you want (such as contacts born in a certain date range), but you won’t need that field in the recordset. You can add that field to the design grid so that you can define criteria, but you should clear the Show check box beneath the field to exclude it from the recordset.
Finally, you can use the Criteria row and the row(s) labeled Or to enter the criteria you want to use as filters. After you understand how a query is put together, you’ll find it easy to specify exactly the fields and records that you want.
The first step in building a query is to select the fields you want in the recordset. You can select the fields in several ways. Using the keyboard, you can tab to a column in the design grid and press Alt+Down Arrow to open the list of available fields. (To move to the design grid, press F6.) Use the Up Arrow and Down Arrow keys to highlight the field you want, and then press Enter to select the field.
Another way to select a field is to drag it from one of the field lists in the upper part of the window to one of the columns in the design grid. In Figure 9-5, the LastName field is being dragged to the design grid. When you drag a field, the mouse pointer turns into a small rectangle.
At the top of each field list in the upper part of the Query window (and also next to the first entry in the Field drop-down list in the design grid) is an asterisk (*) symbol. This symbol is shorthand for selecting “all fields in the table or the query” with one entry on the Field line. When you want to include all the fields in a table or a query, you don’t have to define each one individually in the design grid unless you also want to define some sorting or selection criteria for specific fields. You can simply add the asterisk to the design grid to include all the fields from a list. Note that you can add individual fields to the grid in addition to the asterisk to define criteria for those fields, but you should clear the Show check box for the individual fields so that they don’t appear twice in the recordset.
Inside Out: Another Way to Select All Fields
Another easy way to select all the fields in a table is to double-click the title bar of the field list in the upper part of the Query window—this highlights all the fields. Then click any of the highlighted fields and drag them as a group to the Field row in the design grid. While you’re dragging, the mouse pointer changes to a multiple rectangle icon, indicating that you’re dragging multiple fields. When you release the mouse button, you’ll see that Access 2010 has copied all the fields to the design grid for you.
For this exercise, select ContactID, LastName, FirstName, WorkStateOrProvince, and Birth-Date from the tblContacts table in the Conrad Systems Contacts database. You can select the fields one at a time by dragging them to the design grid. You can also double-click each field name, and Access will move it to the design grid into the next available slot. Finally, you can click on one field you want and then hold down the Ctrl key as you click on additional fields or hold down the Shift key to select a group of contiguous fields. Grab the last field you select and drag them all to the design grid. If you switch the Query window to Datasheet view at this point, you’ll see all the records, containing only the fields you selected from the underlying table.
In general, a field that is output by a query inherits the properties defined for that field in the table. You can define a different Description property (the information that is displayed on the status bar when you select that field in a Query window in Datasheet view), Format property (how the data is displayed), Decimal Places property (for numeric data other than integers), Input Mask property, Caption property (the column heading), and Smart Tags property. We’ll show you the details of how to use the Smart Tag property in Chapter 14.
Note
For details about field properties in client databases, see Chapter 4.
When you learn to define calculated fields later in this chapter, you’ll see that it’s a good idea to define the properties for these fields. If the field in the query is a foreign key linked to another table, you can also set the Lookup properties as described in Chapter 5. Access propagates Lookup properties that you have defined in your table fields; however, you can use the properties on the Lookup tab in the query’s Property Sheet pane to override them.
Note
The Access 2010 query designer lets you define Lookup properties for any text or numeric field (other than AutoNumber). The field doesn’t have to be a defined foreign key to another table. You might find this useful when you want the user to pick from a restricted value list—such as M or F for a Gender field.
To set the properties of a field, click any row of that field’s column in the design grid, and then click the Property Sheet button in the Show/Hide group of the Design contextual tab to display the property sheet, shown in Figure 9-6. Even though the fields in your query inherit their properties from the underlying table, you won’t see those properties displayed here. For example, the BirthDate field in tblContacts has both its Description and Caption set to Birth Date and a Format set to mm/dd/yyyy at the table level. If you click in the BirthDate field in your query and open the property sheet, however, you will see that none of the properties show values. You can use the property settings in the property sheet to override any inherited properties and to customize how a field looks when viewed for this query. Try entering new property settings for the BirthDate field, as shown in Figure 9-6.
Inside Out: Switching Views to Check Field Properties
One of the quickest ways to see if a field in a query has the properties you want is to switch to Datasheet view. If the field isn’t displayed the way you want, you can switch back to Design view and override the properties in the query.
If you make these changes and switch to Datasheet view (click the View button in the Results group and then click Datasheet), you’ll see that the BirthDate column heading is now Birthday; that the date displays day name, month name, day number, and year; and that the text on the status bar matches the new description, as shown in Figure 9-7. When you first view the query results in Datasheet view, you’ll see pound signs (#) in the BirthDate field because Access cannot display all the data in the allotted column width. To fix this issue, grab the right edge of the Birthday header with your mouse and drag it to the right to open the column so that you can see all the date values.
The next step is to further refine the records you want by specifying criteria on one or more fields. The example shown in Figure 9-8 selects contacts working in the state of California.
Figure 9-8. When you specify “CA” as the selection criterion in the design grid, Access returns only records with a WorkStateOrProvince value equal to California.
Entering selection criteria in a query is similar to entering a validation rule for a field, which you learned about in Chapter 4. To look for a single value, simply type it in the Criteria row for the field you want to test. If the field you’re testing is a text field and the value you’re looking for has any blank spaces in it, you must enclose the value in quotation marks. Note that Access adds quotation marks for you around single text values. (In Figure 9-8, we typed CA, but Access replaced what we typed with “CA” after we pressed Enter.)
If you want to test for any of several values, enter the values in the Criteria row, separated by the word Or. For example, specifying CA Or NC searches for records for California or North Carolina. You can also test for any of several values by entering each value in a separate Criteria or Or row for the field you want to test. For example, you can enter CA in the Criteria row, NC in the next row (the first Or row), and so on—but you have to be careful if you’re also specifying criteria in other fields, as explained in AND vs. OR.
Inside Out: Be Careful When Your Criterion Is Also a Keyword
You should be careful when entering criteria that might also be an Access 2010 keyword. In the examples shown here, we could have chosen to use criteria for the two-character abbreviation for the state of Oregon (OR)—but or, as you can see in the examples, is also a keyword. In many cases, Access is smart enough to figure out what you mean from the context. You can enter:
Or Or Ca
in the criteria under State, and Access assumes that the first Or is criteria (by placing quotation marks around the word for you) and the second Or is the Boolean operator keyword. If you want to be sure that Access interprets your criteria correctly, always place double quotation marks around criteria text. If you find that Access guessed wrong, you can always correct the entry before saving the query.
In the section AND vs. OR, you’ll see that you can also include a comparison operator in the Criteria row so that, for example, you can look for values less than (<), greater than or equal to (>=), or not equal to (<>) the value that you specify.
Access 2010 stores dates and times as 8-byte decimal numbers. The value to the left of the decimal point represents the day (day zero is December 30, 1899), and the fractional part of the number stores the time as a fraction of a day, accurate to seconds. Fortunately, you don’t have to worry about converting internal numbers to specify a test for a particular date value because Access 2010 handles date and time entries in several formats.
You must always surround date and time values with pound signs (#) to tell Access that you’re entering a date or a time. To test for a specific date, use the date notation that is most comfortable for you. For example, #April 15, 1962#, #4/15/62#, and #15-Apr-1962# are all the same date if you chose English (United States) in the Regional And Language Options item in the Control Panel. Similarly, #5:30 PM# and #17:30# both specify 5:30 in the evening.
Inside Out: Understanding Date/Time Criteria
You must be careful when building criteria to test a range in a date/time field. Let’s say you want to look at all records between two dates in the ContactEvents table, which has a date/time field—ContactDateTime—that holds the date and time of the contact. For all contact events in the month of January 2011, you might be tempted to put the following on the Criteria line under ContactDateTime.
>=#1/1/2011# AND <=#1/31/2011#
When you look at the results, you might wonder why no rows show up from January 31, 2011 even when you know that you made and recorded several calls on that day. The reason is simple. Remember, a date/time field contains an integer offset value for the date and a fraction for the time. Let’s say you called someone at 9:55 A.M. on January 31, 2011. The internal value is actually 40,574.4132—January 31, 2011 is 40,574 days later than December 30, 1899 (the zero point), and .4132 is the fraction of a day that represents 9:55 A.M. When you say you want rows where ContactDateTime is less than or equal to January 31, 2011, you’re comparing to the internal value 40,574—just the day value, which is midnight on that day. You won’t find the 9:55 A.M. record because the value is greater than 40,574, or later in the day than midnight. To search successfully, you must enter
>=#1/1/2011# AND <#2/1/2011#
When you enter criteria for several fields, all the tests in a single Criteria row or Or row must be true for Access 2010 to include a record in the recordset. That is, Access 2010 performs a logical AND operation between multiple criteria in the same row. So if you enter CA in the Criteria row for StateOrProvince and <#1 JAN 1972# in the Criteria row for BirthDate, the record must be for the state of California and must be for someone born before 1972 to be selected. If you enter CA Or NC in the Criteria row for StateOrProvince and >=#01/01/1946# AND <#1 JAN 1972# in the Criteria row for BirthDate, the record must be for the state of California or North Carolina, and the person must have been born between 1946 and 1971.
Figure 9-9 shows the result of applying a logical AND operator between any two tests. As you can see, both tests must be true for the result of the AND to be true and for the record to be selected.
Figure 9-9. When you specify the logical AND operator between two tests, the result is true only if both tests are true.
When you specify multiple criteria for a field and separate the criteria by a logical OR operator, only one of the criteria must be true for Access 2010 to select the record. You can specify several OR criteria for a field, either by entering them all in a single Criteria cell separated by the logical OR operator, as shown earlier, or by entering each subsequent criterion in a separate Or row. When you use multiple Or rows, if the criteria in any one of the Or rows is true, Access 2010 selects the record. Figure 9-10 shows the result of applying a logical OR operation between any two tests. As you can see, only one of the tests must be true for the result of the OR to be true and for Access 2010 to select the record.
Figure 9-10. When you specify the logical OR operator between two tests, the result is true if either or both of the tests is true.
Inside Out: Don’t Get Confused by AND and OR
It’s a common mistake to get OR and AND mixed up when typing compound criteria for a single field. You might think to yourself, “I want all the work contacts in the states of Washington and California,” and then type WA AND CA in the Criteria row for the WorkStateOrProvince field. When you do this, you’re asking Access to find rows where (WorkStateOrProvince = “WA”) AND (WorkStateOrProvince = “CA”). Because a field in a record can’t have more than one value at a time (can’t contain both the values WA and CA in the same record), there won’t be any records in the output. To look for all the rows for these two states, you need to ask Access to search for (WorkStateOrProvince = “WA”) OR (WorkStateOrProvince = “CA”). In other words, type WA OR CA in the Criteria row under the WorkStateOrProvince field.
Let’s look at a specific example. In Figure 9-11, you specify CA in the first Criteria row of the WorkStateOrProvince field and >=#01/01/1946# AND <#1 JAN 1972# in that same Criteria row for the BirthDate field. (By the way, when you type #1 JAN 1972# and press Enter, Access changes your entry to #1/1/1972#.) In the next row (the first Or row), you specify NC in the WorkStateOrProvince field. When you run this query, you get all the contacts from the state of California born between 1946 and 1971. You also get any records for the state of North Carolina regardless of the birth date.
Figure 9-11. You can specify multiple AND and OR selection criteria in the design grid with additional OR lines.
In Figure 9-12, you can see the recordset (in Datasheet view) that results from running this query.
Figure 9-12. The recordset of the query shown in Figure 9-11 shows only the records that match your criteria.
If you also want to limit rows from contacts in North Carolina to those who were born between 1946 and 1971, you must specify >=#01/01/1946# AND <#1/1/1972# again under BirthDate in the second Or row—that is, on the same row that filters for NC under WorkStateOrProvince. Although this seems like extra work, this gives you complete flexibility to filter the data as you want. You could, for example, include people who were born before 1969 in California and people who were born after 1970 in North Carolina by placing a different criterion under BirthDate in the two rows that filter WorkStateOrProvince.
In addition to comparison operators, Access provides three special operators that are useful for specifying the data you want in the recordset. Table 9-1 describes these operators.
Table 9-1. Criteria Operators for Queries
Predicate | Description |
|---|---|
Between | Useful for specifying a range of values. The clause Between 10 And 20 is the same as specifying >=10 And <=20. |
In | Useful for specifying a list of values separated by commas, any one of which can match the field being searched. The clause In (“CA”, “NC”, “TN”) is the same as “CA” Or “NC” Or “TN”. |
Like[a] | Useful for searching for patterns in text fields. You can include special characters and ranges of values in the Like comparison string to define the character pattern you want. Use a question mark (?) to indicate any single character in that position. Use an asterisk (*) to indicate zero or more characters in that position. The pound-sign character (#) specifies a single numeric digit in that position. Include a range in brackets ([ ]) to test for a particular range of characters in a position, and use an exclamation point (!) to indicate exceptions. The range [0-9] tests for numbers, [a-z] tests for letters, and [!0-9] tests for any characters except 0 through 9. For example, the clause Like“?[a-k]d[0-9]*” tests for any single character in the first position, any character from a through k in the second position, the letter d in the third position, any character from 0 through 9 in the fourth position, and any number of characters after that. |
[a] As you’ll learn in Article 2, “Understanding SQL,” the pattern characters supported by Microsoft SQL Server when you are working in an Access project file are different. The pattern characters discussed here work in desktop applications (.accdb and .accde files) only. | |
Suppose you want to find all contacts in the state of California or Pennsylvania who were born between 1955 and 1972 and whose first name begins with the letter J. Figure 9-13 shows how you would enter these criteria. Figure 9-14 shows the recordset of this query.
Inside Out: Choosing the Correct Date/Time Criteria
If you’re really sharp, you’re probably looking at Figure 9-13 and wondering why we chose Between #1/1/1955# And #12/31/1972# instead of >= #1/1/1955# And < #1/1/1973# to cover the case where the BirthDate field might also include a time. In this case, we know that the BirthDate field has an input mask that doesn’t allow us to enter time values. So we know that using Between and the simple date values will work for this search.
Figure 9-13. You can also restrict records by using Between, In, and Like, all in the same design grid.
Figure 9-14. The recordset of the query shown in Figure 9-13 shows only the records that match your criteria.
Note
For additional examples that use the Between, In, and Like comparison operators, see Defining Simple Field Validation Rules, and the “Predicate” sections in Article 2 on the companion CD.
You can use an expression to combine fields or to calculate a new value from fields in your table and make that expression a new field in the recordset. You can use any of the many built-in functions that Access 2010 provides as part of your expression. You concatenate, or combine, text fields by stringing them end to end, or you use arithmetic operators on fields in the underlying table to calculate a value. Let’s switch to the HousingDataCopy.accdb database to build some examples.
Caution
Do not confuse a calculated expression in a query with the new Calculated data type in Access 2010. Calculated data types store the result of their expression directly in the table, but calculated expressions in queries are calculated only when Access executes the query.
One common use of expressions is to create a new text (string) field by concatenating fields containing text, string constants, or numeric data. You create a string constant by enclosing the text in double or single quotation marks. Use the ampersand character (&) between fields or strings to indicate that you want to concatenate them. For example, you might want to create an output field that concatenates the LastName field, a comma, a blank space, and then the FirstName field.
Try creating a query on the tblEmployees table in the HousingDataCopy.accdb database that shows a field containing the employee last name, a comma and a blank, first name, a blank, and middle name. You can also create a single field containing the city, a comma and a blank space, the state or province followed by one blank space, and the postal code. Your expressions should look like this:
LastName & ", " & FirstName & " " & MiddleName City & ", " & StateOrProvince & " " & PostalCode
You can see the Query window in Design view for this example in Figure 9-15. We clicked in the Field row of the second column and then pressed Shift+F2 to open the Zoom window, where it is easier to enter the expression. Note that you can click the Font button to select a larger font that’s easier to read. After you choose a font, Access 2010 uses it whenever you open the Zoom window again.
Figure 9-15. If you use the Zoom window to enter an expression, you can see more of the expression and select a different font.
Note
Access 2010 requires that all fields on the Field row in a query have a name. For single fields, Access uses the name of the field. When you enter an expression, Access generates a field name in the form ExprN:. See Specifying Field Names, for details about changing the names of fields or expressions. Notice also that Access automatically adds brackets around field names in expressions. It does this so that the field names in the Structured Query Language (SQL) for the query are completely unambiguous. If this table had been designed with blanks in the field names, you would have to type the brackets yourself to ensure that the query designer interprets the names correctly.
When you look at the query result in Datasheet view, you should see something like the one shown in Figure 9-16.
Try typing within the Expr1 field in Datasheet view. Because this display is a result of an expression (concatenation of strings), Access 2010 won’t let you update the data in this column.
Inside Out: Eliminating Extra Spaces When Concatenating Null Values
If you look very closely at Figure 9-16, you can see that we captured the image with the insertion point displayed at the end of the Expr1 calculated field on the first row. Do you notice that there’s an extra space after the first name? This happened because that person has no middle name, so what we’re seeing is the extra blank we inserted after the first name that is supposed to provide spacing between the first and middle name.
This isn’t too much of a problem in this particular expression because you’re not going to notice the extra blank displayed at the end of the name. But if you create the expression First (blank) Middle (blank) Last and if a record has no middle name, the extra blank will be noticeable.
When you use an ampersand, any Null field in the expression doesn’t cause the entire expression to be Null. A little secret: You can also use the arithmetic plus sign (+) to concatenate strings. As you’ll learn when you create arithmetic expressions, if a field in the expression is Null, the expression evaluates to Null. So, to solve the extra blank problem, you can create an expression to concatenate the parts of a name as follows:
FirstName & (" " + MiddleName) & " " & LastNameIf MiddleName is a Null, the arithmetic expression inside the parentheses evaluates to Null, and the extra blank disappears!
In a reservations record (tblReservations in the Housing Reservations database), code in the form that confirms a reservation automatically calculates the correct TotalCharge value for the reservation before Access 2010 saves a changed row. If you strictly follow the rules for good relational table design (see Article 1, “Designing Your Database Application,” on the companion CD), this isn’t normally a good idea, but we designed it this way to demonstrate what you have to code to maintain the calculated value in your table. (Access 2010 won’t automatically calculate the new value for you because we did not define this field using the new Calculated data type.) You can see how this code works in Chapter 25, “Automating Your Application with Visual Basic,” on the companion CD. This technique also saves time later when calculating a total by month or total by facility in a report.
Table 9-2 shows the operators you can use in arithmetic expressions.
Table 9-2. Operators Used in Arithmetic Expressions
Operator | Description |
|---|---|
+ | Adds two numeric expressions. |
– | Subtracts the second numeric expression from the first numeric expression. |
* | Multiplies two numeric expressions. |
/ | Divides the first numeric expression by the second numeric expression. |
\ | Rounds both numeric expressions to integers and then divides the first integer by the second integer. The result is truncated to an integer. |
^ | Raises the first numeric expression to the power indicated by the second numeric expression. |
Mod | Rounds both numeric expressions to integers, divides the first integer by the second integer, and returns only the remainder. |
The expression to calculate the TotalCharge field is complex because it charges the lower weekly rate for portions of the stay that are full weeks and then adds the daily charge for extra days. Let’s say you want to compare the straight daily rate with the discounted rate for longer stays. To begin, you need an expression that calculates the number of days. You can do this in a couple of different ways. First, you can use a handy built-in function called DateDiff to calculate the difference between two Date/Time values in seconds, minutes, hours, days, weeks, months, quarters, or years. In this case, you want the difference between the check-in date and the check-out date in days.
The syntax for calling DateDiff is as follows:
DateDiff(<interval>, <date1>, <date2>[, <firstdayofweek>])The function calculates the difference between <date1> and <date2> using the interval you specify and returns a negative value if <date1> is greater than <date2>. You can supply a <firstdayofweek> value (the default is 1, Sunday) to affect how the function calculates the “ww” interval. Table 9-3 explains the values you can supply for interval.
Note
You can also use the settings you find in Table 9-3 for the interval argument in the DatePart function (which extracts part of a Date/Time value) and DateAdd function (which adds or subtracts a constant to a Date/Time value).
Table 9-3. Interval Settings for DateDiff Function
Setting | Description |
|---|---|
“yyyy” | Calculates the difference in years. DateDiff subtracts the year portion of the first date from the year portion of the second date, so DateDiff |
“q” | Calculates the difference in quarters. If the two dates are in the same calendar quarter, the result is 0. |
“m” | Calculates the difference in months. DateDiff subtracts the month portion of the first date from the month portion of the second date, so DateDiff |
“y” | Calculates the difference in days. DateDiff handles this option the same as “d” below. (For other functions, this extracts the day of the year.) |
“d” | Calculates the difference in days. |
“w” | Calculates the difference in weeks based on the day of the week of <date1>. If, for example, the day of the week of the first date is a Tuesday, DateDiff counts the number of Tuesdays between the first date and the second date. For example, March 23, 2011 is a Wednesday, and March 28, 2011 is a Monday, so DateDiff |
“ww” | Calculates the difference in weeks. When the first day of the week is Sunday (the default), DateDiff counts the number of Sundays greater than the first date and less than or equal to the second date. For example, March 23, 2011 is a Wednesday, and March 28, 2011 is a Monday, so DateDiff |
“h” | Calculates the difference in hours. |
“n” | Calculates the difference in minutes. |
“s” | Calculates the difference in seconds. |
The second way to calculate the number of days is to simply subtract one date from the other. Remember that the integer portion of a date/time data type is number of days. If you’re sure that the fields do not contain any time value, subtract the check-in date from the check-out date to find the number of days. Let’s see how this works in the sample database.
Open the HousingDataCopy.accdb database if you have closed it and start a new query on tblReservations. Add EmployeeNumber, FacilityID, RoomNumber, CheckInDate, CheckOutDate, and TotalCharge to the query design grid. You need to enter your expression in a blank column on the Field row. You’ll build your final expression in two parts so you can understand the logic involved. Using DateDiff, start the expression by entering:
DateDiff("d", [CheckInDate], [CheckOutDate])To calculate the number of days by subtracting, the expression is:
[CheckOutDate] – [CheckInDate]
To calculate the amount owed at the daily rate, multiply either of the previous expressions by the DailyRate field. With DateDiff, the final expression is:
DateDiff("d", [CheckInDate], [CheckOutDate]) * [DailyRate]If you want to use subtraction, you must enter:
([CheckOutDate] – [CheckInDate]) * [DailyRate]
You might be wondering why the second expression includes parentheses. When evaluating an arithmetic expression, Access evaluates certain operations before others, known as operator precedence. Table 9-4 shows you operator precedence for arithmetic operations. In an expression with no parentheses, Access performs the operations in the order listed in the table. When operations have the same precedence (for example, multiply and divide), Access performs the operations left to right.
Table 9-4. Arithmetic Operator Precedence
Access Evaluates Operators in the Following Order: | |
|---|---|
1 | Exponentiation (^) |
2 | Negation—a leading minus sign (–) |
3 | Multiplication and division (*, /) |
4 | Integer division (\) |
5 | Modulus (Mod) |
6 | Addition and subtraction (+, –) |
Access evaluates expressions enclosed in parentheses first, starting with the innermost expressions. (You can enclose an expression in parentheses inside another expression in parentheses.) If you do not include the parentheses in the previous example, Access would first multiply CheckInDate times DailyRate (because multiplication and division occur before addition and subtraction) and then subtract that result from CheckOutDate. That not only gives you the wrong answer but also results in a #NUM! error because you cannot subtract a Double value (the result of multiplying a date/time times a currency) from a date/time value.
After you select the fields from the table and enter the expression to calculate the total based on the daily rate, your query design grid should look something like Figure 9-17.
When you switch to Datasheet view, you can see the calculated amount from your expression, as shown in Figure 9-18.
Note that not all the calculated amounts are larger than the amount already stored in the record. When the reservation is for six days or fewer, the daily rate applies, so your calculation should match the existing charge. You might want to display only the records where the new calculated amount is different than the amount already stored. For that, you can add another expression to calculate the difference and then select the row if the difference is not zero.
Switch back to Design view and enter a new expression to calculate the difference in an empty column. Your expression should look like this:
TotalCharge – (([CheckOutDate] – [CheckInDate]) * [DailyRate])
In the Criteria line under this new field, enter <> 0. Your query design should look like Figure 9-19, and the datasheet for the query now displays only the rows where the calculation result is different, as shown in Figure 9-20.
Figure 9-20. The datasheet now shows only the rows where the calculation is different than the stored value.
Finding the rows that differ in this way has the added benefit of displaying the calculated difference. If you’re interested only in finding the rows that differ but don’t care about the amount of the difference, you don’t need the second expression at all. You can find the rows you want by placing the expression <>[TotalCharge] in the Criteria line under the first expression you entered. This asks Access to compare the amount calculated at the straight daily rate with the value in the TotalCharge field stored in the record and display the row only when the two values are not equal.
You might have inferred from the earlier discussion about entering criteria that you can use only constant values in the Criteria and Or lines. As you can see, you can also compare the value of one field or expression with another field or expression containing a reference to a field.
Inside Out: Adding Parentheses to Expressions for Clarity
You might have noticed that we placed an extra set of parentheses around the original expression we built to calculate the amount at the daily rate before subtracting that amount from the stored value. If you study Table 9-4 carefully, you’ll see that we really didn’t have to do this because Access would perform the multiplication before doing the final subtract. However, we find it’s a good practice to add parentheses to make the sequence of operations crystal clear—we don’t always remember the order of precedence rules, and we don’t want to have to go looking up the information in Help every time we build an expression. Adding the parentheses makes sure we get the results we want.
So far, you have built fairly simple expressions. When you want to create a more complex expression, sometimes the Expression Builder can be useful, as discussed in the next section.
For more complex expressions, Access 2010 provides a utility called the Expression Builder. In Chapter 6, you learned about the Expression Builder when you created Calculated data types and validation rules. You also learned how to use the new IntelliSense feature built into the Expression Builder in Access 2010. In this section, we’ll show you additional ways the Expression Builder can help you construct expressions.
Let’s say you want to double-check the total amount owed for a reservation in the sample database. You have to work with several fields to do this—CheckInDate, CheckOutDate, DailyRate, and WeeklyRate. You need to calculate the number of weeks to charge at the WeeklyRate and then charge the remaining days at the DailyRate. To see how the Expression Builder works, start a new query on the tblReservations table. Click in an empty field in the design grid, and then click the Builder button in the Query Setup group of the Design contextual tab. Access opens the Expression Builder dialog box, shown in Figure 9-21.
In the upper part of the dialog box is a blank text box in which you can build an expression. You can type the expression yourself, but it’s sometimes more accurate to find field names, operators, and function names in the three panes in the lower part of the dialog box.
In Access 2010, the Expression Builder shows the functions, constants, and operators applicable to the current context. For example, in Figure 9-21, Access shows you only the list of functions, constants, and operators you can use for expressions in a client query under the Expression Elements pane on the far left. Access also lists the name of your database file along with all the field names and objects contained within your database. The middle pane, Expression Categories, changes based on what you select in the Expression Elements pane. For example, if you click Operators under Expression Elements, the Expression Categories pane lists several group names of operators you can use in your expression. The right pane, Expression Values, shows you all possible values you can use based on the currently highlighted category selected in the middle pane. The expression you need to build, which we’ll walk you through in detail in the next few pages, will ultimately look like this:
((DateDiff("d", [tblReservations]![CheckInDate], [tblReservations]![CheckOutDate]) \
7) * [WeeklyRate]) + ((DateDiff("d", [tblReservations]![CheckInDate], [tblReservation
s]![CheckOutDate]) Mod 7) * [DailyRate])You can use the Expression Builder in a couple of different ways to help you correctly construct this expression. Start by double-clicking the Functions category in the left pane, then select Built-In Functions to see the list of function categories in the center pane and the list of functions within the selected category in the right pane. Select the Date/Time category in the center pane to narrow down the choices. Here, you can see the DateDiff function (which you used earlier) as well as several other built-in functions you can use. (You can find a list of the most useful functions and their descriptions in Article 4, “Visual Basic Function Reference,” on the companion CD.)
Double-click the DateDiff function in the right pane to add it to the expression text box at the top of the Expression Builder. When you add a function to your expression in this way, the Expression Builder shows you the parameters required by the function. Access also displays the parameters of the function and additional information about the DateDiff function at the bottom of the dialog box. You can click any parameter to highlight it in the text box at the top of the Expression Builder and type a value or select a value from one of the lists in the bottom panes. Click <<interval>> and overwrite it with “d”. (See Table 9-3 for a list of all the possible interval settings.) You’ll notice that Access displays a ScreenTip beneath the expression after you overwrite <<interval>>, as shown in Figure 9-22. Access highlights which parameter you are currently working on in the ScreenTip.
Figure 9-22. Access displays the parameters for the expression in a ScreenTip as you fill in the expression.
Inside Out: Moving the Expression Builder ScreenTip
If the ScreenTip beneath the expression obstructs your view, you can move it to a different location on the screen. When you place the mouse pointer over the ScreenTip, the pointer changes to a four-arrow cross-hair, and you can then drag the ScreenTip to a new location.
You need to insert the CheckInDate field from tblReservations for <<date1>> and the CheckOutDate field for <<date2>>. Click <<date1>> to highlight it and double-click HousingDataCopy in the left pane to open the list of objects in the database. Click the plus sign next to Tables to expand the list to show all the tables in this database. Scroll down until you find tblReservations and select it to see the list of field names in the second pane. Double-click CheckInDate. Then click <<date2>>, and double-click CheckOutDate. You don’t need the <<firstweekday>> or <<firstweek>> parameters, so click them and press the Delete key to remove them. (You can also remove the extra commas, such as after the CheckOutDate field, if you like.) The Expression Builder should now look like Figure 9-23. Note that in Figure 9-23, we expanded the height of the Expression Builder dialog box so you can see more objects listed in the Expression Elements pane.
You’ll notice that the Expression Builder pastes [tblReservations]![CheckInDate] into the expression area, not just CheckInDate. There are two good reasons for this. First, the Expression Builder doesn’t know whether you might include other tables in this query and whether some of those tables might have field names that are identical to the ones you’re selecting now. The way to avoid conflicts is to fully qualify the field names by preceding them with the table name. When working in queries, separate the table name from the field name with a period or an exclamation point. Second, you should enclose all names of objects in Access in brackets ([ ]). If you designed the name without any blank spaces, you can leave out the brackets, but it’s always good practice to include them.
Inside Out: Understanding Name Separators in SQL
As you’ll learn in Chapter 25, in most cases you should separate the name of an object from the name of an object within that object (for example, a field within a table) with an exclamation point. When you build an expression in the Expression Builder, you’ll find that the Expression Builder separates names using exclamation points. However, as you’ll learn in Article 2, the standard for the SQL database query language uses a period between the name of a table and the name of a field within the table. To be most compatible with the SQL standard when constructing a query expression, use a period between a table name and a field name. Access accepts either an exclamation point or a period in query design.
Next, you need to divide by 7 to calculate the number of weeks. You’re not interested in any fractional part of a week, so you need to use the integer divide (\) operator. You can either type the operator, or scroll down in the leftmost pane, select Operators to open that list, select Arithmetic operators in the second pane, and then double-click the integer divide operator (\) in the rightmost list to add it to your expression. Make sure the insertion point in the expression box is positioned after the integer divide operator and then type the number 7.
The next operation you need is to multiply the expression you have thus far by the WeeklyRate field from tblReservations. If you like, you can add left and right parentheses around the expression before adding the multiply operator and the field. Remember from Table 9-4 that multiplication and division are of equal precedence, so Access evaluates the division before the multiplication (left to right) even if you don’t add the parentheses. But, as we noted earlier, we like to make the precedence of operations crystal clear, so we recommend that you add the parentheses. Press the Home key to go to the beginning of the expression, add a left parenthesis, press the End key to go to the end, add a right parenthesis, double-click the multiply operator (*) in the rightmost list, and finally select the field WeeklyRate from the tblReservations field list.
Note
WeekyRate and DailyRate are currency fields. DateDiff returns an integer, and the result of an integer divide (\) or a modulus (Mod) operation is an integer. Whenever you ask Access to evaluate an arithmetic expression, it returns a result that has a data type sufficiently complex to contain the result. As you might expect, multiplying an integer (a simple data type) with a currency field (a more complex data type) returns a currency field.
You need to add this entire expression to the calculation for remaining days at the daily rate, so press Ctrl+Home again and add one more left parenthesis, press the Ctrl+End key, and add one more right parenthesis to complete this first part of the expression. Double-click the addition operator (+) in the rightmost list to add it to your expression. Rather than scan back and forth to add parentheses as we build the second part of the expression, add two more left parentheses to start building the calculation for extra days. You need to create another DateDiff expression, but this time we’ll utilize the IntelliSense feature of the Expression Builder. Type the letter d and Access shows you a list of functions that all start with that letter, as shown in Figure 9-24.
If you continue typing letters, Access reduces the list of functions to match the letters you are typing. If no functions or objects exist with the letters you type, Access dismisses the list. (You can also dismiss the list yourself by pressing Esc at any time.) Use the down arrow to highlight the DateDiff function and then press Enter or Tab. Access writes the rest of the letters for the DateDiff function for you, adds a left parenthesis, and displays the arguments for the DateDiff function in a screens tip, as shown in Figure 9-25. Note that in Figure 9-25, we clicked Less to collapse the bottom part of the Expression Builder dialog box to focus just on writing the expression. To expand the dialog box, click the button again, now labeled More, and Access shows you the three panes in the bottom part of the dialog box.
Figure 9-25. Access can finish typing your function name when you select it from the IntelliSense drop-down list of functions.
Type “d” for the first parameter of the DateDiff function followed by a comma. Next, you need to add the CheckInDate field again from the tblReservations table for the date1 parameter. Type the letters tb and Access displays a list of all the table names in this database. Use the down arrow to highlight tblReservations and press Enter or Tab to select it. To view a list of all the field names in the tblReservations table, you can either type a period (.) or exclamation point (!). Remember, as discussed earlier, that Access accepts either an exclamation point or a period in query design. Type a period and Access displays all the fields in the tblReservations table, as shown in Figure 9-26. You’ll notice in Figure 9-26 that Access adds brackets around the table name.
Press Enter or Tab and Access adds the CheckInDate field to the expression because it is the first field in the drop-down list of field names. Type a comma to move to the second parameter, type tb again to bring up a list of the tables, use the down arrow to highlight the tblReservations table, and then press Enter or Tab to add the table name to the expression. Now, type a period to display a list of all the field names in the tblReservations table, use the down arrow to highlight the CheckOutDate field, and then press Enter or Tab to add the field to your expression. You don’t need the last two parameters of the DateDiff function—firstweekday and firstweek—so end this part of the expression by typing a right parentheses. Your expression up to this point should look like Figure 9-27.
Figure 9-27. After you finish the second DateDiff function, your expression should match this figure.
Now, you need to know how many days beyond full weeks are in the reservation. You might be tempted to divide by 7 again and try to extract the remainder, but there’s a handy operator that returns only the remainder of a division for you—Mod. Scroll down in the left pane and select Operators. In the middle pane, select Arithmetic to see only the arithmetic operators in the right pane. Double-click Mod to add it to your expression after the parentheses.
We’re almost done. Type the number 7, add a right parentheses to close the Mod calculation, and then double-click the multiply operator button in the right pane. The last step you need to take is to add the DailyRate field from the tblReservations table to complete the expression. Type tb to display the list of table names, use the down arrow to highlight tblReservations, and then press Enter or Tab to add it to the expression. Type a period to display a list of field names for the tblReservations table, use the down arrow to highlight the DailyRate field, and then press Enter or Tab. Type one last right parentheses to finish the expression. Verify that your completed expression exactly matches the one in Figure 9-28.
Figure 9-28. Your completed expression in the Expression Builder dialog box should match this figure.
Click OK to paste your result into the design grid. Go ahead and add ReservationID, FacilityID, RoomNumber, CheckInDate, CheckOutDate, and TotalCharge to your query grid. When you switch to Datasheet view, your result should look like Figure 9-29.
Do you notice any stored values that don’t match what you just calculated? (Hint: Look at the highlighted row.) If you haven’t changed the sample data, you’ll find several rows that we purposefully updated with invalid TotalCharge values. Here’s a challenge: Go back to Design view and enter the criteria you need to display only the rows where your calculated charge doesn’t match the TotalCharge stored in the table. You can find the solution saved as qxmplUnmatchedCharges in the HousingDataCopy.accdb sample database.
We used the DateDiff function to solve this problem, but Access 2010 has several other useful functions to help you deal with date and time values. For example, you might want to see only a part of the date or time value in your query. You might also want to use these functions to help you filter the results in your query. Table 9-5 explains each date and time function and includes filter examples that use the ContactDateTime field in the tblContactEvents table in the Conrad Systems Contacts sample database.
Table 9-5. Date and Time Functions
Description | Example | |
|---|---|---|
Day(date) | Returns a value from 1 through 31 for the day of the month. | To select records with contact events that occurred after the 10th of any month, enter Day([ContactDateTime]) in an empty column on the Field line and enter >10 as the criterion for that field. |
Month(date) | Returns a value from 1 through 12 for the month of the year. | To find all contact events that occurred in March (of any year), enter Month([ContactDateTime]) in an empty column on the Field line and enter 3 as the criterion for that field. |
Year(date) | Returns a value from 100 through 9999 for the year. | To find contact events that happened in 2011, enter Year([ContactDateTime]) in an empty column on the Field line and enter 2011 as the criterion for that field. |
Weekday(date) | As a default, returns a value from 1 (Sunday) through 7 (Saturday) for the day of the week. | To find contact events that occurred between Monday and Friday, enter Weekday([ContactDateTime]) in an empty column on the Field line and enter Between 2 And 6 as the criterion for that field. |
Hour(date) | Returns a value from 0 through 23 for the hour of the day. | To find contact events that happened before noon, enter Hour([ContactDateTime]) in an empty column on the Field line and enter <12 as the criterion for that field. |
DateAdd(interval, amount, date) | Adds an amount in the interval you specify to a date/time value. | To find contact events that occurred more than six months ago, enter <DateAdd(“m”, –6, Date()) as the criterion under ContactDateTime. (See also the Date function below.) |
DatePart(interval, date) | Returns a portion of the date or time, depending on the interval code you supply. Useful interval codes are “q” for quarter of the year (1 through 4) and “ww” for week of the year (1 through 53). | To find contact events in the second quarter, enter DatePart(“q”, [ContactDateTime]) in an empty column on the Field line, and enter 2 as the criterion for that field. |
Date() | Returns the current system date. | To select contact events that happened more than 30 days ago, enter <(Date() – 30) as the criterion under ContactDateTime. |
Every field must have a name. By default, the name of a simple field in a query is the name of the field from the source table. However, when you create a new field using an expression, the expression doesn’t have a name unless you or Access assigns one. You have seen that when you create an expression in the Field row of the design grid, Access adds a prefix such as Expr1 followed by a colon—that is the name that Access is assigning to your expression. Remember, the column heading for the field is, by default, the field name unless you specify a different caption property setting. As you know, you can assign or change a caption for a field in a query by using the field’s property sheet.
You can change or assign field names that will appear in the recordset of a query. This feature is particularly useful when you’ve calculated a value in the query that you’ll use in a form, a report, or another query. In the queries shown in Figure 9-15, Figure 9-17, and Figure 9-19, you calculated a value and Access assigned a temporary field name. You can replace this name with something more meaningful. For example, in the first query, you might want to use something like FullName and CityStateZip. In the second query, RecalculatedCharge might be appropriate. To change a name generated by Access, replace ExprN with the name you want in the Field row in the query design grid. To assign a new name to a field, place the insertion point at the beginning of the field specification and insert the new name followed by a colon. Figure 9-30 shows the first query with the field names changed.
Figure 9-30. You can change the Expr1 and Expr2 field names shown in Figure 9-16 to display more meaningful field names.
Note that we could have made the column headings you see even more readable by also assigning a caption to these fields via the field’s property sheet. We might have chosen something like Person Name for the first field and City-State-Zip for the second field. Keep in mind that setting the caption does not change the actual name of the field when you use the query in a form, a report, or Visual Basic code.
Normally, Access 2010 displays the rows in your recordset in the order in which they’re retrieved from the database. You can add sorting information to determine the sequence of the data in a query. Click in the Sort row for the field you want to sort on, click the arrow in this row, and then select Ascending or Descending from the list. In the example shown in Figure 9-31, the query results are to be sorted in descending order based on the calculated NewTotalCharge field. (Note that we have given the calculated field a field name.) The recordset will list the most expensive reservations first. The resulting Datasheet view is shown in Figure 9-32. You can find this query saved as qryXmplChargeCalcSorted in the HousingDataCopy.accdb sample database.
Figure 9-32. Datasheet view shows the recordset of the query shown in Figure 9-31 sorted on the NewTotalCharge field.
Inside Out: Why Specifying Sort Criteria Is Important
When Access 2010 solves a query, it tries to do it in the most efficient way. When you first construct and run a query, Access might return the records in the sequence you expect (for example, in primary key sequence of the table). However, if you want to be sure Access always returns rows in this order, you must specify sort criteria. As you later add and remove rows in your table, Access might decide that fetching rows in a different sequence might be faster, which, in the absence of sorting criteria, might result in a different row sequence than you intended.
You can also sort on multiple fields. Access honors your sorting criteria from left to right in the design grid. If, for example, you want to sort by FacilityID ascending and then by NewTotalCharge descending, you should include the FacilityID field to the left of the NewTotal-Charge field. If the additional field you want to sort is already in the design grid but in the wrong location, click the column selector box (the tinted box above the field row) to select the entire column and then click the selector box again and drag the field to its new location. If you want the field that is out of position to still appear where you originally placed it, add the field to the design grid again in the correct sorting sequence, clear the Show check box (you don’t want two copies of the field displayed), and set the Sort specification. Figure 9-33 shows the query shown in Figure 9-31 modified to sort first by FacilityID and then by NewTotalCharge, but leave FacilityID displayed after ReservationID. We saved this query in the HousingDataCopy.accdb sample database as qxmplChargeCalcSortedTwo.
Figure 9-33. This example sorts on two fields while maintaining the original field sequence in the query output.
Inside Out: A Reminder: Why Lookup Properties Can Be Confusing
If you open the datasheet of qxmplChargeCalcSortedTwo and scroll down in the recordset, you’ll find the Facility column sorted as Main Campus Housing A, Main Campus Housing B, South Campus Housing C, and North Satellite Housing D. Why does South appear before North if the values are supposed to be sorted in ascending order? Remember, in Chapter 5, that we warned you about Lookup properties confusing the display you see. The information you’re seeing in the datasheet comes from the Lookup defined on the FacilityID column in tblReservations—you’re seeing the related facility name from tblFacilities. However, the actual value of FacilityID is a number. You can click on the FacilityID column, open the field’s property sheet, click the Lookup tab, and set the Display Control property to Text Box to see the actual number value. When you do this and look at the datasheet again, you’ll see that the values are sorted correctly.