The Expression Builder is the dialog in which you design your expression.
To open the Expression Builder
- Open your Query
- Choose an column in your Query, or click Add to add a new blank column
- On the Field row of that column, click the [...] button
Understanding the Expression Builder dialog
The dialog is split into two main sections, the "Expression" section at the top, and the "Elements" section below.
The Expression section
The Expression section contains:
Output Type
The Output Type defines the datatype of the output of the expression. The Output Type can be:
- Currency
- Date/Time
- Integer
- Number
- Text
- Yes/No
The system will always attempt to convert your expression to the chosen Output Type, but you may get blank values if you choose an inappropriate Output Type.
Expression area
Below the Output Type is a large white area where you enter your expression.
The Elements section
The Elements section is there to make it easier to build your expression, but strictly speaking isn't required - you could simply type your expression in to the expression area above. However to help get the syntax correct there are a set of elements displayed to allow you to simple select them and they will then be added to your expression.
Operators
There is a row of buttons representing operators. Clicking a button inserts the operator into the expression at the current cursor position.
The operators are generally self-explanatory, however note that for joining text strings together you need to use the || operator, and not the mathematical + operator.
For more details of the operators see the Operator Reference.
Data Sources
Below the operators, there are two columns. The left column provides a tree of options, and selecting one shows related items in the right column.
The Data Sources lists all the Tables and other Data Sources that have been added to the Query. Clicking on a Data Source name then shows all the fields from it in the right column.
Double click on a field to add it to the expression.
Functions
Similarly there are several categories of Function that can be added to an expression.
Click on a category, and the functions are listed on the right.
Click on a function name to see help text explaining its purpose and syntax.
Double click on a function name to add it to the expression.
For more details of the functions see the Function Reference.
Working with the Expression Builder
If you click on a function in the expression area it will be selected. If you click again it will be unselected, and the insertion point placed where you clicked.
With a function or other content selected in the editor, if you then double click on a function in the list below to insert it, the selected content will be used as the first parameter of the inserted function. This makes it very fast to apply multiple functions to a value, by simply double clicking on the relevant functions.
Complex expressions
You can combine as many functions in your expression as you need.
Functions may be nested.
The IIF function provides conditional content (and can itself be nested if required)
Simple expressions
An expression may be a single field reference (in which case, for clarity, the Query interface will show it as such, rather than as an expression)
An expression may be a constant value, ie "Welcome" or 27. (These can be useful when you need to construct a link in a Query, using the expression to define the "Click here..." text of the link)
Learn more
Further information is available on working with expressions.
