Use the Expression Builder

Expressions are used in many different contexts in Access, and are similar to formulas in Excel. The Expression Builder provides easy access to the names of the fields and controls in your database, and to many of the built-in functions that are available to you when you write expressions. You can use as many or as few of these features as you want, depending on your own needs or preferences.

In this article

Why use the Expression Builder?

Tools to help you enter expressions

IntelliSense and Quick Tips

Quick Info and Help

Overview of the Expression Builder

Expression box

Expression elements, categories, and values

Add an element to an expression by using the element lists

Why use the Expression Builder?

Expressions consist of functions, operators, constants, and identifiers (for example, the names of fields, tables, forms, and queries). The Expression Builder enables you to easily look up and insert these components, and thereby enter expressions more quickly and accurately. In addition, the Expression Builder helps you determine which components are appropriate for the context in which you are typing the expression. This is particularly helpful when building Web databases or Web Apps, in which only certain functions are available for use.

For more information about Web databases, see the article Build a database to share on the Web.

For more information about Web apps, see the article Create an Access App.

You can use the Expression Builder to create a new expression, or you can select from prebuilt expressions, including expressions for displaying page numbers, the current date, and the current date and time.

You can start the Expression Builder from most of the places where you would write expressions manually, such as the Control Source property of a control, or the Validation Rule property of a table field. As a rule, if you see the Build button when you click in a property box, or if you see the word expression in a menu, you can click it to start the Expression Builder.

Tools to help you enter expressions

Access has tools that help you enter expressions more quickly and accurately. These tools are available not only in the Expression Builder, but also in the property sheet and most other places where you can enter expressions.

IntelliSense and Quick Tips

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.

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.

The following illustration shows the IntelliSense list and a Quick Tip.

1. IntelliSense displays possible functions and other identifiers as you type an expression.

2. Quick Tips display a short description of the selected item.

Quick Info and Help

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. Optional arguments are enclosed in square brackets ([]). While the Quick Info is displayed, you can click on the function name to open a Help topic for more information.

1. Quick Info displays the syntax of the function. Click the function name to open a Help topic about the function.

2. Optional arguments are enclosed in square brackets. The argument that you are currently typing is displayed in bold text.

Important:  Do not confuse the square brackets that indicate optional arguments with the square brackets that enclose identifiers in the actual expression.

Top of Page

Overview of the Expression Builder

In many cases, the tools listed in the previous section are enough to help you enter your expression. However, you might also benefit from other tools that the Expression Builder provides, as described in this section.

The following illustration calls out the major parts of the Expression Builder:

1. Instructions and Help link    Get information about the context in which you are entering the expression.

2. Expression box    Type your expression here, or add expression elements by double-clicking items in the element lists below.

Note:  If you do not see the element lists (items 3, 4, and 5), click More >> to the right of the Expression box.

3. Expression Elements list    Click an element type to view its categories in the Expression Categories list.

4. 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 box.

5. Expression Values list    Double-click a value to add it to the Expression box.

6. Help and information about the selected expression value    If available, click the link to see a Help article about the selected expression value.

Top of Page

Expression box

The upper section of the Expression Builder contains a box where you construct your expression. You can type your expression in the box manually, using IntelliSense and the other tools described in the previous section. Or, if you want, you can use the three element lists below the box to select and add elements.

Note:  If you do not see the three lists in the lower section of the Expression Builder, on the right side of the Expression Builder dialog box, click More >>.

Expression elements, categories, and values

When expanded, the lower section of the expression builder contains three lists from which you can select elements for your expression.

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

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

• The Expression Values list displays the values, if any, for the elements and categories that you selected in the left and middle lists. For example, if you click Built-In Functions in the left list and then click a function category in the middle list, the Expression Values list displays all the built-in functions for the selected category.

Add an element to an expression by using the element lists

1. Click an item in the Expression Elements list. For example, to insert a built-in function, expand Functions, and then click Built-in Functions.
   
   The middle and right lists change to display the appropriate values.
   

2. If applicable, click a category in the middle list. For this example, click Program Flow.
   
   The right list changes to display the appropriate values.

3. Double-click an item in the right list. For this example, double-click IIf.
   
   Access adds the item to the expression box. In this example, Access adds IIf (<<expression>>, <<truepart>>, <<falsepart>>).
   

4. Replace any placeholder text with valid argument values. Placeholder text is indicated by angle brackets (<< >>). In this example, the placeholders are <<expression>>, <<truepart>>, and <<falsepart>>.

   • In the expression box, click the placeholder, and then either type the argument manually or select an element from the element lists.
     
     

     Tip:  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.

5. If the expression contains other elements, they may be separated by the placeholder <<Expr>>. You must replace this placeholder with an operator before the overall expression will be valid.

Top of Page