The expression we have created in the Expression Box now appears as shown in Illustration 11.

Illustration 11: Our Newly Assembled Expression in the Expression Box

26.         Click OK.

The Select Query dialog appears as shown in Illustration 12.

Illustration 12: Partial View of the Select Query Dialog, with New Expression Shown (compressed)

The expression we have created is in place, although it may be compressed, as shown. We can always reopen the Expression Builder to review or edit the expression by right-clicking and selecting Build ... from the context menu, should the need arise.

27.         Right-click the field containing the expression we have created.

28.         Select Build... from the context menu that appears.

29.         Carefully type the caption Net Price: over the existing Expr1:, changing nothing else in the expression.

The Expression Box should now appear as shown in Illustration 13.

Illustration 13: The Expression, after Modification of Caption

30.         Click OK.

31.         Select View --> Datasheet View from the top menu.

(The Datasheet View icon at the left of the Access toolbar, just below the File menu item (as shown in Illustration 14) is an alternative means of achieving the same action.)

Illustration 14: Icon Selection Option for Selecting Datasheet View

The view changes to display the results of the calculated field (Illustration 15) for the entire column. A quick check of the math indicates that the correct Net Price is reflected.

Illustration 15: Datasheet View of the Calculated Field

32.         If desired, Select File --> Save as and save the query, with a meaningful name and location.

Our options abound at this point; we can convert the query to a Make Table query, or insert the calculation into an existing table or report in other ways. MS Access offers us a rich set of options in the creation and use of calculated fields.

Conclusion ...

With this tutorial article, Create a Calculated Field with the Expression Builder, we have begun the new MS Access for the Business Environment series. Our objective in this lesson was to introduce calculated fields in MS Access, focusing on the practical steps of their creation in a simple business scenario. We briefly explored the concept of calculated fields, introduced the MS Access Expression Builder as a straightforward means of creating and editing them, and provided a practical walkthrough of the process of creating a sample Net Price calculated field.

» See All Articles by Columnist William E. Pearson, III