Access 2010: The Missing Manual

Query Functions

By now, it may have crossed your mind that you can manipulate numbers and text in even more ambitious ways—ways 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 is 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 (Understanding the SQL View), which is the language it uses to perform data operations.

Using a Function

Whether you’re using the simplest or the most complicated ...