ACC: Cannot Sum Calculated Controls in Forms or Reports
ID: Q113354
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SYMPTOMS
Moderate: Requires basic macro, coding, and interoperability skills.
When you open a form or report, "#Name?" is displayed in a text box in a
form footer, or a parameter box appears requesting the field specified in
the sum expression in a report.
CAUSE
The text box is trying to perform a sum on a calculated control in the
form or report and because Microsoft Access does not store calculated
values, it cannot sum the calculated field.
RESOLUTION
There are two workarounds to this behavior:
- Repeat the calculation being computed in the calculated control in the
sum expression. If the calculated control on the form or report has the
expression
=[UnitPrice] * [Quantity]
repeat this calculation in the sum expression as:
=SUM([UnitPrice] * [Quantity])
NOTE: In versions 1.x and 2.0, there is a space in Unit Price.
- Base the form or report on a query and calculate the expression as a
calculated field in the query, rather than as a calculated control on
the form or report. Because the calculation is being computed in the
query, the result will be available for other computations. This method
is considerably faster than the first method because the computation
does not have to be repeated. If the calculated control on the form or
report has the expression
=[UnitPrice] * [Quantity]
then the new calculated field in the query will have the following in
the Field row of the query grid:
ExtendedPrice: [UnitPrice] * [Quantity]
If the form or report is based on a query with this calculated field,
ExtendedPrice will appear in the field list and should be used in place
of the calculated control on the form or report. The sum expression in
the form or report footer would look like the following:
=SUM([ExtendedPrice])
MORE INFORMATION
Steps to Reproduce Behavior
- Open the sample database Northwind.MDB (or NWIND.MDB in versions 1.x
and 2.0).
- Open the Orders Subform form in Design view.
- Add a text box with the following properties to the detail section:
Name: My Extended Price
ControlSource: =[UnitPrice] * [Quantity]
- Change the Order Subtotal text box ControlSource property from
=Sum([ExtendedPrice])
to:
=Sum([My Extended Price])
NOTE: The original expression will correctly display the sum of the
ExtendedPrice field because this field is being computed as a
calculated field in the Order Details Extended query the form is based
on. The ExtendedPrice calculated field in the Order Details Extended
query looks like the following:
ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1-
[Discount])*100)/100
- Change the form's Default View property from Datasheet to Single Form.
- View the form in Form view. Note that "#Name?" appears in the Order
Subtotal box in the form footer.
Workaround 1: Repeating the Calculation in the Sum Expression
NOTE: These steps continue from the "Steps to Reproduce Behavior" section
above.
Change the Order Subtotal ControlSource property from
=Sum([My Extended Price])
to:
=Sum([UnitPrice] * [Quantity])
Note that the calculation is correctly displayed in the Order Subtotal box
in the form footer.
Workaround 2: Repeating the Calculation in the Sum Expression
- Open the Order Details Extended query in Design view.
- In an empty query grid column, type the following line in the Field row:
My Extended Price: [Order Details].[UnitPrice]*[Quantity]
- Remove the My Extended Price calculated control in the detail section.
Note that the calculation is correctly displayed in the Order Subtotal
box in the form footer.
REFERENCES
For more information about using calculated controls in forms and reports,
search the Help Index for "Calculated Controls," or ask the Microsoft
Access 97 Office Assistant.
Keywords : kberrmsg FmsSubf
Version : 1.0 1.1 2.0 7.0 97
Platform : WINDOWS
Issue type : kbprb