Sometimes, writing an expression can be daunting. But the Expression Builder makes it a lot easier. Expressions have many components or "moving parts": functions, operators, constants, identifiers, and values. Use the Expression Builder to quickly look up these components and insert them accurately. There are two ways to use the Expression Builder: use the Expression Builder box, which may be all you need, or use the expanded Expression Builder, when your expression is more elaborate.
In this article
First things first, how do I find it?
Although the Expression Builder is available from many locations in Access, the most consistent way to display it is to put focus on a property box that takes an expression, such as Control Source or Default Value, and then click Expression Builder or press CTRL+F2.
In a Macro, click .
Tip If you see the word expression in a menu, you can click it to start the Expression Builder.
See it in action
The following video shows how to use the Expression Builder to create a common expression for a calculated field.
Using the Expression Builder box
The Expression Builder box helps you build expressions more quickly and accurately with smart tools and context-sensitive information. If you see the expanded Expression Builder, click Less >> to just display the Expression Builder box.
IntelliSense and Quick Tips
1 IntelliSense (Access 2010 or later) dynamically displays possible functions and other identifiers as you type an expression.
As soon as you begin typing an identifier or function name, IntelliSense displays a drop-down list of possible values. You can continue typing, or you can double-click the correct value in the list to add it to the expression. Alternatively, you can use the up- and down-arrow keys to select the value you want, and then press TAB or ENTER to add it to your expression. For example, if you begin typing the word “Format”, the IntelliSense list displays all the functions that begin with “Format”.
Tip To hide the IntelliSense drop-down list, press ESC. To show it again, press CTRL+SPACEBAR.
2 Quick Tips display a short description of the selected item.
While the IntelliSense list is displayed, a short description, or Quick Tip, appears to the right of the currently selected item. The first item in the list is selected by default, but you can select any item in the list to view its Quick Tip. The Quick Tip can help you determine the purpose of a function, or what kind of control or property the item is.
Quick Info and Help
1 Use Quick Info to display the syntax of the function, and click the function name to open a Help topic about the function.
While you are typing a function in an expression, the Quick Info feature displays the syntax of the function, so that you know exactly which arguments are required for the function.
2 Optional arguments are enclosed in square brackets (). The argument that you are currently typing is displayed in bold text. Don't confuse the square brackets that indicate optional arguments with the square brackets that enclose identifiers in the actual expression.
Using the expanded Expression Builder
The expanded Expression Builder helps you look up and insert functions, operators, constants, and identifiers (for example, field names, tables, forms, and queries), saving time and reducing errors. If all you see is the Expression Builder box, click More >> to see the expanded Expression Builder, .
1 Use instructions and a Help link to get information about the context in which you are entering the expression.
2 In the Expression Builder box, type your expression here, or automatically add expression elements by double-clicking items in the lists below. These lists work together as a hierarchy to help you drill down to the desired expression component.
3 In the Expression Elements list, click an element type to view its categories in the Expression Categories list.
The Expression Elements list displays the top-level elements that are available for you to build an expression, such as database objects, functions, constants, operators, and common expressions. The contents of this list are different depending on the context you are in. For example, if you are typing an expression in the Control Source property of a form, the list contains different items than if you are typing an expression in the Validation Rule property of a table.
Tip To use pre-built expressions, including displaying page numbers, the current date, and the current date and time, select Common Expressions.
4 In the Expression Categories list, click a category to view its values in the Expression Values list. If there are no values in the Expression Values list, double-click the category item to add it to the Expression Builder box.
The Expression Categories list contains specific elements or categories of elements for the selection that you make in the Expression Elements list. For example, if you click Built-In Functions in the Expression Elements list, the Expression Categories list displays function categories.
5 In the Expression Values list, double-click a value to add it to the Expression Builder box.
The Expression Values list displays the values, if any, for the elements and categories that you previously selected. For example, if you clicked Built-In Functions in the Expression Elements list and then clicked a function category in the Expression Categories list, the Expression Values list displays all the built-in functions for the selected category.
6 To see Help and information about the selected expression value, if available, click the link.
A step-by-step example
The following example shows how to use the Expression Elements, Categories, and Values in the expanded Expression Builder to create an expression.
Click items in the Expression Elements list, such as Functions, and then Built-in Functions.
Click a category in the Expression Categories list, such as Program Flow.
Double-click an item in the Expression Values list, such as IIf which is added to the Expression Builder box:
IIf (<<expression>>, <<truepart>>, <<falsepart>>)
Placeholder text is indicated by angle brackets (<< >>).
Replace any placeholder text with valid argument values. You can enter the values manually or continue to select an element from the three lists.
To view a Help topic that contains more information about the valid arguments for a function, select the function in the Expression Values list, and then click the link at the bottom of the Expression Builder.
If the expression contains other elements, they may be separated by the following placeholder:
Replace this placeholder to make the overall expression valid.