Microsoft KB Archive/113354

= ACC: Cannot Sum Calculated Controls in Forms or Reports =

Article ID: 113354

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q113354



Moderate: Requires basic macro, coding, and interoperability skills.



SYMPTOMS
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])



<div class="moreinformation_section">

Steps to Reproduce Behavior
 Open the sample database Northwind.MDB (or NWIND.MDB in versions 1.x and 2.0).</li> Open the Orders Subform form in Design view.</li>  Add a text box with the following properties to the detail section:

<pre class="fixed_text">     Name: My Extended Price ControlSource: =[UnitPrice] * [Quantity]

</li>  Change the Order Subtotal text box ControlSource property from

<pre class="fixed_text">     =Sum([ExtendedPrice])

to:

<pre class="fixed_text">     =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:

<pre class="fixed_text">     ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1- [Discount])*100)/100

</li> Change the form's Default View property from Datasheet to Single Form.</li> View the form in Form view. Note that "#Name?" appears in the Order Subtotal box in the form footer.</li></ol>

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

<pre class="fixed_text">  =Sum([My Extended Price])

to:

<pre class="fixed_text">  =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

 * 1) Open the Order Details Extended query in Design view.
 * 2) In an empty query grid column, type the following line in the Field row:

My Extended Price: [Order Details].[UnitPrice]*[Quantity]
 * 1) 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.

<div class="references_section">