Video: Create an expression

Expressions in Access are similar to formulas in Excel; you can use them throughout a database to calculate values, enforce data validation rules, filter data, and more. This video shows you the basics of creating an expression.

In the video

Add a calculated field to a table

This procedure uses an expression to create a calculated field in a table.

In the Navigation Pane, double-click the table to which you want to add the calculated field.

Scroll to the rightmost column in the table, and then click Click To Add in the column header.

Point to Calculated Field, and then click the type of data that you want to store in the new field.

Access opens the Expression Builder.

In the Expression Builder dialog box, if the three Expression Element lists are not displayed, click More >> to display them.

Click or double-click items in the Expression Elements, Expression Categories, and Expression Values lists to select the identifiers, functions, and operators you want. Alternatively, type directly in the Expression Box at the top of the Expression Builder, and select the items you want from the IntelliSense lists that appear as you type. In the video, we concatenate the Last Name field with the First Name field, including a comma and a space between the two fields, by entering the following expression:

=[Last Name] & “, “ & [First Name]

When the expression is complete, click OK to add the new calculated field to the table.

Add a calculated text box to a form or report

On forms and reports, you can enter expressions directly into the property sheet, or you can click the Build button to open the Expression Builder.

In the Navigation Pane, right-click the form or report that you want to modify, and then click Layout View.

On the Design tab, in the Controls group, click the Text Box control

Click on the form or report where you want to place the new control.

If the Property Sheet is not already displayed, press F4 to display it.

Make sure the new Text Box control is selected, and then on the All tab of the Property Sheet, click in the Control Source property box.

Type an equal sign (=), and then type the first few letters of the function or identifier with which you want to begin the expression. Access will display a list of items that you can select from. To add one of the items to your expression, you can either double-click it, or use the arrow keys to select it in the list, and then press TAB or ENTER. In the video, we create two calculated text boxes, with the following expressions:

=[List Price] * 0.08

=Mid([Product Name],19)

Save the form or report, and then on the Home tab, in the Views group, click View, and then click Form View or Report View.

Use an expression as a query criterion

In the Query Builder, you can use expressions in the Criteria row to filter data based on a calculation.

Create a new query or open an existing query in Design View.

In the Criteria row for the field that you want to filter, Type the first few letters of the function or identifier with which you want to begin the expression. Access will display a list of items that you can select from. To add one of the items to your expression, you can either double-click it, or use the arrow keys to select it in the list, and then press TAB or ENTER. If you prefer to use the Expression Builder, on the Design tab, in the Query Setup group, click Builder.