You can use the Expression Builder to help build expressions. The Expression Builder is a tool you can start from most places in Access where you write expressions, such as control properties in forms and reports, field properties in tables, and in the query design grid. It offers easy access to the names of fields and controls in your database, as well as many of the built-in functions available to you when writing expressions.
Tip New features such as AutoText and Quick Info have been added to the Expression Builder in Access 2010, making it easier than ever to create expressions. Read an article or try Office 2010!
With the Expression Builder, you can create an expression from scratch, or you can select from some prebuilt expressions for displaying page numbers, the current date, and the current date and time.
The upper section of the Expression Builder contains an expression box where you construct your expression. You use the three columns in the lower section of the Expression Builder to locate elements that you can paste into the expression box. You can also type parts of the expression directly into the expression box. Thus, you can construct an expression by combining some amount of typing and pasting.
The middle section of the Expression Builder displays buttons for commonly used operators. To insert an operator in the expression box, click the appropriate button. To display a list of operators you can use in expressions, click the Operators folder in the lower-left column, and then click the appropriate category in the middle column. The lower-right column then lists all of the operators available in the selected category. To insert an operator, select it and click Paste.
The lower section of the Expression Builder 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 and user-defined functions, constants, operators, and common expressions.
- The middle column lists specific elements or categories of elements for the folder 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 you selected in the left and middle columns. For example, if you click Built-In Functions in the left column and click a function category in the middle column, the right column lists all of the built-in functions available in the selected category.
You construct your expression by typing text and pasting elements from the other areas within the Expression Builder into the expression box. For example, you can click on the lower-left column to see any of the objects in your database, as well as functions, constants, operators and common expressions. When you click an item in the left column, the content of the other columns (middle and right) changes accordingly.
For example, when you click the name of a table in the left column, the middle column lists the fields in that table.
When you double-click Functions and then click Built-In Functions, the middle column lists all of the function categories, and the right column list the functions themselves.
After you insert a function into your expression, the expression box displays both the function and text indicating the arguments that the function needs. You can then replace that text with the correct argument values.
When you paste an identifier into your expression, the Expression Builder only inserts 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 the Customers form and 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 of the context of the form, you must include the full identifier: Forms![Customers].Visible.
To start the Expression Builder in a table, form or report
- Click the property or action argument box that will contain the expression.
- Click the Build button next to the property.
To start the Expression Builder in a query
- 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 the Field cell for the column where you want to create a calculated field.
- Click the Build button on the toolbar.
You can think of the Expression Builder as a way to look up and insert things you might have trouble remembering, such as identifier names (fields, tables, forms, queries and so on) and function names and arguments.