Section 7.2. Query Functions
7.2. Query FunctionsBy now, it may have crossed your mind that you can manipulate numbers and text in even more ambitious waysways that go beyond what the basic operators let you do. You may want to round off numbers or capitalize text. Access does include a feature that lets you take your expressions to the next level, and it's called functions . A function's a built-in algorithm that takes some data that you supply, performs a calculation, and then returns a result. The difference between functions and the mathematical operators you've already seen is the fact that functions can perform far more complex operations. Access has a catalog with dozens of different functions, many of which perform feats you wouldn't have a hope of accomplishing on your own. Functions come in handy in all sorts of interesting places in Access. You can use them in:
As you explore the world of functions, you'll find that many are well suited to calculated fields but not filter conditions. In the following sections, you'll see exactly where each function makes most sense. Note: Functions are a built-in part of the Access version of SQL (Section 6.2.3), which is the language it uses to perform data operations. 7.2.1. Using a FunctionWhether you're using the simplest or the most complicated function, the syntax the rules for using a function in an expressionis the same. To use a function, simply enter the function name , followed by parentheses. Then, inside the parentheses, put all the information the function needs in order to perform its calculations (if any). For a good example, consider the handy Round( ) function, which takes a fractional number and then tidies up any unwanted decimal places. Round( ) is a good way to clean up displayed values in a calculated field. You'll see why Round( ) is useful if you create an expression like this, which discounts prices by five percent: SalePrice: [Price] * 0.95 Run a price like $43.97 through this expression, and you wind up with 41.7715 on the other sidewhich doesn't look that great on a sales tag. The Round( ) function comes in handy here. Just feed it the unrounded number and the number of decimal places you want to keep: SalePrice: Round([Price] * 0.95, 2) Technically, the Round() function requires two pieces of information, or arguments . The first's the number you want to round (in this case, it's the result of the calculation Price * 0.95 ), and the second's the number of digits that you want to retain to the right of the decimal place (2). The result: the calculation rounded to two decimal places, or 41.77. Note: Most functions, like Round( ), require two or three arguments. However, some functions can accept many more, while a few don't need any arguments at all.
7.2.1.1. Nested functionsYou can use more than one function in a single calculated field or filter condition. The trick is nesting : nerdspeak for putting one function inside another. For example, Access provides an absolute-value function named Abs( ) that converts negative numbers to positive numbers (and leaves positive numbers unchanged). Here's an example that divides two fields and makes sure the result is positive: Speed: Abs([DistanceTravelled] / [TimeTaken]) If you want to round this result, you place the entire expression inside the parentheses for the Round( ) function, like so: Speed: Round (Abs([DistanceTravelled] / [TimeTaken]), 2) When evaluating an expression with nested functions, Access evaluates the innermost function first. Here, it calculates the absolute value, and then rounds the result. In this example, you could swap the order of these steps without changing the result: Speed: Abs(Round([DistanceTravelled] / [TimeTaken], 2)) In many other situations, the order you use is important, and different nesting produces a different result. Nested functions can get ugly fast. Even in a relatively simple example like the speed calculation, it's difficult to tell what's going on without working through the calculation piece by piece. And if you misplace a bracket , the whole calculation can be thrown off. When you need to nest functions, it's a good idea to build them up bit by bit, and run the query each time you add another function into the mix, rather than try to type the whole shebang at once. 7.2.2. The Expression BuilderFunctions are a great innovation, but Access just might have too much of a good thing. Access provides a catalog of dozens of different functions tailored for different tasks , some of which are intended for specialized mathematical or statistical operations. Note: This book doesn't cover every Access function. (If it did, you'd be fighting to stay awake.) However, in the following sections you'll see the most useful functions for working with numbers, text, and dates. To discover even more functions, use the Expression Builder. Or, if you prefer to do your learning online, check out the pithy resource www.techonthenet.com/access/functions. To quickly find the functions you want, Access provides a tool called the Expression Builder. To launch the Expression Builder, follow these steps:
Note: When you use the Expression Builder to add a function, it adds placeholders (like <number> and <precision>) where you need to supply the arguments. Replace this text with the values you want to use. Most Access experts find that the Expression Builder is too clunky to be worth the trouble. But even though the Expression Builder may not be the most effective way to write an expression, it's a great way to learn about new and mysterious functions, thanks to its built-in function reference. If you find a function that sounds promising but you need more information, select it in the list and then click Help. You'll be rewarded with a brief summary that explains the purpose of the function and the arguments you need to supply, as shown in Figure 7-6.
7.2.3. Formatting NumbersFormat( ) is one interesting mathematical function, which transforms numbers into text. Format( ) is interesting because the text it creates can be formatted in several different ways, which allows you to control exactly how your numbers are presented. To understand the difference, think back to the expression you used earlier for discounting product prices: SalePrice: [Price] * 0.95 Even if the Price field has the Currency data type, the calculated values in the SalePrice field appear as ordinary numbers (without the currency sign, thousands separator, and so on). So you see 43.2 when you might prefer $43.20. You can remedy this problem by using the Format( ) function to apply a currency format: SalePrice: Format([Price] / 0.95, "Currency") Now the calculated values include the currency sign. Even better, since currencies are displayed with just two decimal places, you no longer need to clean up fractional values with the Round( ) function. The trick to using the Format( ) function is knowing what text to supply for the second argument in order to get the result you want. Table 7-2 spells out your options. Table 7-2. Formatting Options
7.2.4. More Mathematical FunctionsThe mathematical functions in Access don't get much respect, because people don't need them terribly often. You've already seen Round( ) and Format( )the most useful of the bunchbut there are still a few others that Access mavens turn to from time to time in calculated fields. They're listed in Table 7-3. Table 7-3. Functions for Numeric Data
7.2.5. Text FunctionsSo far, all the functions you've seen have worked with numeric data. However, there's still a lot you can do with text. Overall, there are three ways you can manipulate text:
Table 7-4 shows the most common functions people use with text. Table 7-4. Functions for Text
Using these functions, you can create a calculated field that shows a portion of a long text value, or changes its capitalization. However, how you can use these functions in a filter expression may not be as obvious. You could create a filter condition that matches part of a text field, instead of the whole thing. Here's an example of a filter condition that selects records that start with Choco : Left([ProductName], 5) = "Choco" Figure 7-7 shows how you enter this filter condition.
The Len( ) function's a bit of an oddity. It examines a text value and returns numeric information (in this case, the number of characters in the value, including all spaces, letters, numbers, and special characters). The Len( ) function isn't too useful in a simple calculated expression, because you'll rarely be interested in the number of letters in a text value. However, it does let you write some interesting filter conditions, including this one that grabs records with a Description of less than 15 characters (which probably could use some extra information): Len(Description) < 15 7.2.6. Date FunctionsYou've already seen how you can use simple addition and subtraction with dates (Section 7.1.2.2). However, you can accomplish a whole lot more with some of Access's date functions. Without a doubt, everyone's favorite date functions are Now( ) and Date( ), which you first saw in Chapter 4 (Section 4.3.2.3). These functions grab the current date and time, or just the current date. You can use these functions to create queries that work with the current year's worth of orders. Here's a filter condition that uses Date( ) to select projects that are past due: =<Date() Add this to the Criteria box for the DueDate field, and you'll see only those records that have a DueDate that falls on or before today. Date logic becomes even more powerful when paired with the DatePart( ) function, which extracts part of the information in a date. DatePart( ) can determine the month number or year, letting you ignore other details (like the day number and the time). Using DatePart( ) and Date( ), you can easily write a filter condition like this one, which selects all the orders placed in the current month:
DatePart("m", [DatePlaced])=DatePart("m", Date( )) And DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date( )) This rather lengthy expression's actually a combination of two conditions joined by the And keyword. The first condition compares the month of the current date with that of the date stored in the DatePlaced field: DatePart("m", [DatePlaced])=DatePart("m", Date()) This expression establishes that they're the same calendar month, but you also need to make sure it's the same year: DatePart("yyyy", [DatePlaced])=DatePart("yyyy", Date()) The trick to using DatePart( ) (and several other date functions) is understanding the concept of date components . As you can see, using the text m with the DatePart( ) functions gets the month number, and using the text yyyy extracts a four-digit year. Table 7-5 shows all your options. Table 7-5. Date Components
You use the date components with several date functions, including DatePart( ), DateAdd( ), and DateDiff( ). Table 7-6 has these and more useful date- related functions. Table 7-6. Functions for Dates
Tip: Access has other date functions that provide part of the functionality of DatePart( ). One example's Month( ), which extracts the month number from a date. Other duplicate functions include Year( ), Day( ), Hour( ), Minute( ), and Second( ). These functions don't add any advantages, but you may see them used in other people's queries to get an equivalent result. 7.2.7. Dealing with Blank Values (Nulls)Databases have two types of fields: required and optional. Ordinarily, fields are optional (as discussed in Section 4.1.1), which means a sloppy person can leave a lot of blank values. These blank values are called nulls , and you need to handle them carefully . If you want to write a filter condition that catches null values, simply type this text into the criteria box: Is Null This condition matches any fields that are left blank. Use this on the CustomerID field in the Orders table to find any orders that aren't linked to a customer. Or ignore unlinked records by reversing the condition, like so: Is Not Null Sometimes, you don't want to specifically search for (or ignore) null values. Instead, you want to swap those values with something more meaningful to the task at hand. Fortunately, there's an oddly named tool for just this task: the Nz( ) function. The Nz( ) function takes two arguments. The first's a value (usually a query field) that may contain a null value. The second parameter's the value that you want to show in the query results if Access finds a null value. Here's an example that uses Nz( ) to convert null values in the Quantity field to 0: Nz([Quantity], 0) Converting to 0 is actually the standard behavior of Nz( ), so you can leave off the second parameter if that's what you want: Nz([Quantity]) At this point, you may not be terribly impressed at the prospect of changing blank values in your datasheet into zeroes. But this function's a lifesaver if you need to create calculated fields that work with values that could be null. Consider this innocent-seeming example: OrderItemCost: [Quantity] * [Price] This expression runs into trouble if Quantity is null. Nulls have a strange way of spreading, somewhat like an invasive fungus. If you have a null anywhere in a calculation, the result of that calculation is automatically null. In this example, that means the OrderItemCost for that record becomes null. Even worse , if the OrderItemCost enters into another calculation or a subtotal , that too becomes null. Before you know it, your valuable query data turns into a ream of empty cells . To correct this problem, use the Nz( ) function to clean up any potential nulls in optional fields: OrderItemCost: Nz([Quantity]) * Nz([Price]) Finally, you can use Nz( ) to supply a different value altogether. In a text field, you may choose to enter something more descriptive. Here's an example that displays the text [Not Entered] next to any record that doesn't include name information: Name: Nz([FirstName] & [LastName], "[Not Entered]") |
![Access 2007[c] The Missing Manual](/icons/blank_book.jpg "Access 2007[c] The Missing Manual"){kind=icon}