Work with Expressions and the Expression Builder

Work with Expressions

You can add calculations to queries, forms, and reports by typing an expression or formula that tells Access exactly what to calculate.

An expression can be any combination of fields, values, and operators that results in a value. Here's an example of an expression that calculates profit from two fields called Income and Expenses:

Profit: [Income] — [Expenses].

This expression calculates profit from two fields called Income and Expenses.

You can also use constants in an expression, such as:

Commission: [Sales] * .15

Number fields aren't the only types of fields that you can use in expressions—you can also perform calculations with dates, times, and text data. Here's an example of an expression that combines text:

Agent:[FirstName]&" "&[LastName]

Use the Expression Builder

Expressions can be hard to write. You need to specify the exact name of fields and tables, sometimes enclose information in brackets, and so on. If you know what you want an expression to do, but don't know how to write it, you can try using the Expression builder. The Expression builder lets you pick the fields, mathematical symbols, and functions required to create an expression.

1. In Design View, click the Field row of a blank column in the design grid.
2. Click the Builder button on the ribbon.

   You can also open the Expression Builder by right-clicking an empty field in the design grid and selecting Build from the contextual menu.

   

   The Expression Builder appears. The Expression Builder makes it easy to create an expression by selecting the field and operators you want to use in an expression.

3. Select a field to use in the calculation.
   
4. Select an operation for the calculation.
   
5. Click or type any other fields or values you want to use.
6. Click OK.
   

   The Expression Builder closes.

   Now you'll want to add a meaningful label to the new calculated field.

7. In the new calculated field, replace the Expr1: label with a more meaningful field name.
8. Save and run the query.
   

Access displays the results of the query.

Microsoft Access is very strict about how you write your expressions. If your expressions aren't written in the correct syntax, they won't work. Use the following table as a guideline for adding fields, text, and constants to your expressions.