Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.
Often, writing an expression can be arduous. Thankfully, the Expression Builder simplifies it. Expressions have several components or “moving parts”: functions, operators, constants, identifiers, and values. With the Expression Builder, you can rapidly look up these components and add them correctly. There are two ways to apply the Expression Builder: use the Expression Builder box, which may be merely what you require, or use the expanded Expression Builder, once your expression is meaningful.
In this article
First things first, how do I find it?
Even though the Expression Builder is available from several locations in Access, the most common way to access it is to emphasise focus on a property box that accepts an expression, such as Control Source or Default Value, and then choose Expression Builder or press CTRL+F2.
In a Macro, press .
Tip: If you see the word expression in a menu, you can select it to start the Expression Builder.
See it in action
The following video highlights how to use the Expression Builder to produce a common expression for a calculated field.
Using the Expression Builder box
The Expression Builder box aids you construct expressions more efficiently and accurately with smart tools and context-sensitive information. If you notice the expanded Expression Builder, choose Less >> to simply display the Expression Builder box.
IntelliSense and Quick Tips
1 IntelliSense (Access 2010 or later) dynamically shows possible functions and other identifiers as you input an expression.
As soon as you begin entering an identifier or function name, IntelliSense presents a drop-down list of possible values. You can keep typing, or you can double-click the correct value in the list to insert it to the expression. Alternatively, you can use the up- and down-arrow keys to pick your desired value, and then press TAB or ENTER to join 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 display it again, press CTRL+SPACEBAR.
2 Quick Tips display a short description of the specified item.
While the IntelliSense list is presented, a short description, or Quick Tip, emerges to the right of the currently chosen item. The first item in the list is picked automatically, but you can choose any item in the list to view its Quick Tip. The Quick Tip can guide you to establish the purpose of a function, or what kind of control or property the item is.
Quick Info and Help
1 Use Quick Info to showcase the syntax of the function, and press the function name to access a Help topic about the function.
While you are entering a function in an expression, the Quick Info feature illustrates the syntax of the function, so that you know exactly which arguments are needed for the function.
2 Optional arguments are wrapped in square brackets (). The argument that you are currently typing is shown in bold text. Don’t confuse the square brackets that symbolise optional arguments with the square brackets that enclose identifiers in the actual expression.
Using the expanded Expression Builder
The expanded Expression Builder assists you to lookup and apply 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, choose More >> to see the expanded Expression Builder, .
1 Employ instructions and a Help link to receive information about the context in which you are inputting the expression.
2 In the Expression Builder box, enter your expression here, or automatically append expression elements by double-clicking items in the lists below. These lists collaborate as a hierarchy to aid you delve into the sought expression component.
3 In the Expression Elements list, choose an element type to check its categories in the Expression Categories list.
The Expression Elements list presents 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 variable according to 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, pick 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 has 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 presents function categories.
5 In the Expression Values list, double-click a value to insert it to the Expression Builder box.
The Expression Values list presents the values, if any, for the elements and categories that you previously selected. For example, if you pressed Built-In Functions in the Expression Elements list and then selected a function category in the Expression Categories list, the Expression Values list shows all the built-in functions for the chosen category.
6 To see Help and information about the selected expression value, if available, select 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.
- Pick items in the Expression Elements list, such as Functions, and then Built-in Functions.
- Select a category in the Expression Categories list, such as Programme 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 (<< >>).
- Update any placeholder text with valid argument values. You can type the values manually or continue to choose an element from the three lists.
- To see a Help topic that includes more information about the valid arguments for a function, choose the function in the Expression Values list, and then pick the link at the bottom of the Expression Builder.
- If the expression has other elements, they may be split by the following placeholder:
<<Expr>>Substitute this placeholder to validate the overall expression.