Microsoft Office Tutorials and References
In Depth Information
Chapter 4: Doing Calculations in Forms and Subforms (and Reports)
A calculated value is a value that Access creates by doing a calculation based
on other information, usually by using fields from your tables. Access can
add the product total to the shipping cost for an order to come up with the
total cost, for example.
To include a calculated value on a form or report when the calculation isn’t
already stored in a calculated column in a table, create a text box and then
enter an expression in the Control Source property of the text box. An
expression is a formula that tells Access how to calculate an answer from
field values and other values. Expressions start with an equal sign (=). If
field names include spaces, enclose them in square brackets. (Actually, we
enclose all field names in square brackets, just so we don’t forget.) Following
is an example expression:
= [Product Total] + [Shipping Cost]
Here’s another one:
= “Your total will be “ & [GrandTotal] & “.”
The expressions you use on forms and reports are the same as the
expressions you use to create calculated columns in tables and calculated fields in
queries. Turn to Book II, Chapter 1 for information on using calculated
columns in tables, and see Book III, Chapter 2 to find out how expressions work
in queries, including the operators and functions they can include.
Making a calculated control
A calculated control is a control that uses an expression rather than a field
name as its Control Source property (as explained in Chapter 2 of this
minibook). Usually, it’s a text box control. To create a calculated control,
follow these steps:
1. With the form or report open in Design or Layout view, click the Text
Box button in the Controls group on the Design tab of the Ribbon.
For an introduction to the tools in the Controls group, see Chapter 2 of
this minibook. You have to use buttons in the Controls group, rather
than the field list, to create a control with a blank control source. (A
control with no control source is called an unbound control. )
2. Click the form where you want the text box to appear, and drag to the
right and down to form a rectangle.
The rectangle becomes a text box when you release the mouse button.
In Design view, the text box displays Unbound; in Layout view, it’s blank.
The control has no control source; Access doesn’t know what to display
in the text box.