Using the Expression Builder

You don't always have to type your calculated field expressions. The Expression Builder lets you construct expressions with just a few clicks of the mouse. It even has a built in checker, so you'll know right away if you've made a mistake.

To create a calculated field with the Expression Builder:

1. With the query open in Design view, click the field where you want to add the calculated field.
2. Click the Build toolbar button (or right-click and select Build... from the shortcut menu).
3. If the query already includes the fields you want to use in your expression, they will be listed in the middle box of expression elements.
4. If you want to add a field from another table or query, expand the appropriate object list until you see the field you want is displayed in the middle box.
5. Double-click the field name to add it to your expression.
6. Click the mathematical or other operator that you want.
7. Continue building your expression until you are finished. Click OK to add the expression to your query.

When you create a new calculated field, by default, Access will set the Show option to off. Be sure to check the Show check box, or your new field won't show when you run the query.

When you create a calculated field with the Expression Builder, Access will automatically name the field "Expr1". Rename the field by clicking in the field name box and typing the name you want. Be sure not to delete the colon after the name — Access needs this to know that this is a calculated field.

You can create some very sophisticated expressions with the Expression Builder. Be sure to explore the features available in the Functions and Common Expressions folders.