The Expression Builder

Functions 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.

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.

To quickly find the functions you want, Access provides a tool called the Expression Builder. To launch the Expression Builder, follow these steps:

1. Open a query in Design view.
2. Right-click the box where you want to insert your expression, and then choose Build.
   If you're creating a calculated field, then you need to right-click the Field box. If you're creating a filter condition, then you need to right-click the Criteria box.
   Once you choose Build, the Expression Builder appears, showing any content that's currently in the box.
3. Add or edit the expression.
   The Expression Builder includes two shortcuts that you'll want to try. You can insert a name without typing it by hand and you can find a function by browsing. Note: The Expression Builder is an all-purpose tool to create expressions for calculated fields and filter conditions. Some options make sense only in one context. The logical operators like the equals (=) symbol and the And, Or, Not, and Like operators are useful for setting criteria for filtering but don't serve any purpose in calculated fields.
   Note: When you insert field names in the Expression builder, they're written in a slightly lengthier format that always includes the table name. You'll see [Products]![Price] instead of just [Price]. Don't worry both mean the same thing to Access.
4. Click OK.
   Access copies your new expression back into the Field box or Criteria box.

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.