You can use the Expression Builder to help build expressions. 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.
In this article
Understanding the Expression Builder
You can think of the Expression Builder as a way to look up and insert components of an expression that you might have trouble remembering, such as identifier names (for example, fields, tables, forms, and queries), and function names and their arguments.
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 in Microsoft Office Access 2007 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 , you can click it to start the Expression Builder.
1. Expression box
The upper section of the builder contains an expression box where you construct your expression. You can type your expression in the box manually, or you can select elements from the three columns in the lower section of the Expression Builder and then add them to your expression box. To add an element, double-click it, and then click Paste.
2. Operator buttons
The middle section of the Expression Builder displays buttons for inserting common arithmetical and logical operators into your expression. To insert an operator in the expression box, click the appropriate button. To display a longer list of operators that you can use in expressions, click the Operators folder in the lower-left column that contains expression elements, and then click the category that you want in the middle column. The right column then lists all the operators in the selected category. To insert an operator, double-click it.
3. Expression elements
The lower section contains three columns:
The left column displays folders that list the tables, queries, forms, and reports in your database, as well as the available built-in functions and user-defined functions, constants, operators, and common expressions.
The middle column lists specific elements or categories of elements for the folder that is selected in the left column. For example, if you click Built-In Functions in the left column, the middle column lists function categories.
The right column lists the values, if any, for the elements that you selected in the left and middle columns. For example, if you click Built-In Functions in the left column and then click a function category in the middle column, the right column lists all the built-in functions for the selected category.
To construct your expression, you can type text in the expression box and paste elements from the other areas in the Expression Builder. For example, you can click in the lower-left column to see any of the objects in your database, in addition to the available functions, constants, operators, and common expressions. When you click an item in the left column, the contents of the other columns change accordingly. For example, when you double-click Tables in the left column and then click the name of a table, the middle column lists the fields in that table. When you double-click Functions in the left column and then click Built-In Functions, the middle column lists all the function categories, and the right column lists all the functions for those categories. When you double-click a function to insert it into your expression, the function and the text that indicates the needed arguments for that function appear as placeholder text in the expression box. For example, if you double-click the IIf function, the Expression Builder adds the following to the expression box:
IIf (<< expr >>, << truepart >>, << falsepart >>)
You must replace the text <<expr>>, <<truepart>>, and <<falsepart>>with the actual argument values. You can do this directly in the expression box by clicking each placeholder, and then either typing the argument or selecting the argument by using the three expression element lists.
If you double-click two or more functions in succession, for example, the IIf function and then the Round function, the Expression Builder adds both functions to the expression box, separated by the placeholder <<Expr>>:
IIf (<< expr >>, << truepart >>, << falsepart >>) << Expr >> Round (<<number>>, <<precision>>)
In addition to replacing the placeholders for the function arguments, You must replace the placeholder <<Expr>> with an operator before the overall expression will be valid.
When you paste an identifier (the name of a table field or control) into your expression, the Expression Builder inserts only the parts of the identifier that are required in the current context. For example, if you start the Expression Builder from the property sheet of a form called Customers, and you then paste an identifier for the Visible property of the form into your expression, the Expression Builder pastes only the property name Visible. If you use this expression outside the context of the form, you must include the full identifier: Forms![Customers].Visible.
Start the Expression Builder from a table
In the Navigation Pane, right-click the table that you want to change, and then click Design View on the shortcut menu.
In the Field Name column, click the field that you want to change.
Under Field Properties, click the General tab.
Click the property that will contain the expression, and then click the Build button next to the property.
Start the Expression Builder from a form or report
In the Navigation Pane, right-click the form or report that you want to change, and then click Design View on the shortcut menu.
Right-click the control that you want to change, and then click Properties on the shortcut menu.
In the property sheet, locate the property that will contain the expression, and then click the Build button next to the property.
Start the Expression Builder from a query
In the Navigation Pane, right-click the query that you want to change, and then click Design View on the shortcut menu.
Click the cell in the design grid that will contain the expression. For example, click the Criteria cell for the column where you want to supply criteria, or click the Field cell for the column where you want to create a calculated field.
On the Design tab, in the Query Setup group, click Builder.