Search:  

Previous pageData Integration Next page
Expressions 

Introduction

Expressions provide a powerful way to manipulate and combine the values from fields, to create a 'calculated field'.

Generally in Queries, each column takes its value from a single field in one of the data sources. For example a Name column would be based on the Name field in a Person Table.

However with expressions you are not restricted to one source field.

For example you could have a Name column which combines the FirstName and LastName fields.

[Person]![FirstName] || [Person]![LastName]

Expressions can also be mathematical.

For example if you have a Product Price field, and a Tax rate field (containing say 20 for 20% tax), you can create an expression to show the Product Price including the tax.

[Product]![Price] * (1+([Tax]![Rate]/100))

Expressions never change the underlying data stored in the data sources, instead they provide new values that can then be used elsewhere, for example in the various Views.

Expression Builder

The Expression Builder dialog provides an easy interface to construct expressions, with pick lists of fields, operators and functions and help on the syntax of the functions.

For details of the available functions see the Function Reference.

For details of the operators see the Operators Reference.

Working with Expressions

When working with expressions there are particular rules regarding field Titles (you need them) and Union Queries (output types must match). Learn more...

Expressions vs Regular Expressions

Expressions should not be confused with Regular Expressions. Expressions are described in this section, whereas Regular Expressions provide a method for searching and replacing values within text strings using a terse syntax.

If you apply both an Expression and a Regular Expression to a field in a Query, the Expression is calculated first, and the Regular Expression works on the output from the Expression.

Managing data