Query Functions
By 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:
- Calculated fields. To add information to your query results.
- Filter conditions. To determine what records you see in a query.
- Visual Basic code. The all-purpose extensibility system for Access that you'll tackle in Part Five.
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, which is the language it uses to perform data operations.
Using a Function
Whether 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.
Nested functions
You 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.