Microsoft KB Archive/207763

= ACC2000: You Cannot Sum Calculated Controls in Forms or Reports =

Article ID: 207763

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q207763



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

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SYMPTOMS
When you open a form or a report, you may see the following behavior:
 * #Error or #Name? is displayed in a text box in the form footer.
 * A parameter box prompts you for the field that is specified in a sum expression within a report.



CAUSE
You are trying to have the text box sum a calculated control in the form or the report. Because Microsoft Access does not store calculated values, Access cannot sum the calculated field.



RESOLUTION
To work around this behavior, use one of the following two methods. You can use Method #1 only with .mdb files. You can use Method #2 with both .mdb and .adp files.

Method #1: Repeat the Calculation within the Sum Expression
Place the calculated expression within the sum expression. To see how this works, open the Order Subform form in the sample database Northwind.mdb in Design view, and then change the ControlSource property of the Order Subtotal text box from:

=Sum([ExtendedPrice])

with the calculation placed inside of the Sum function as follows:

=Sum([UnitPrice] * [Quantity])

View the form in Form view. Note that the calculation is correctly displayed in the Order Subtotal text box in the form footer. Close the Orders Subform form without saving the changes.

Method #2: Compute the Calculated Expression in a Query or in a View
If you compute the calculation, the results will be available for other computations. This method is faster than the first method because the computation does not have to be repeated. To see how this works, follow these steps:  In the sample database Northwind.mdb or in the sample project NorthwindCS.adp, open the Order Details Extended query or view in Design view. Note that the expression in Northwind.mdb is as follows:

ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100

Note that the expression in NorthwindCS.adp is as follows:

CONVERT(money, [Order Details].UnitPrice*[Order Details].Quantity*(1-[Order Details].Discount)/100)*100

Because these expressions perform their computations at the query level or at the view level, you can now sum the results of this expression at the form level. Close the query and open the Orders Subform form in Design view. Note that the Order Subtotal text box in the form footer has the following expression:

=Sum([ExtendedPrice])

 Set the Default View property of the form to Single Form. Because this form is based on the Order Details Extended query, the calculated field, ExtendedPrice, appears in the field list, and you can use it for summing values. Note that the calculation is correctly displayed in the Order Subtotal text box in the form footer.</ol>

<div class="moreinformation_section">

Steps to Reproduce the Behavior
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

<ol> Open the sample database Northwind.mdb.</li> Open the Orders Subform form in Design view.</li> Add a text box to the detail section, and then set the following properties:

Name: My Extended Price

ControlSource: =[UnitPrice] * [Quantity]

</li> Change the ControlSource property of the Order Subtotal text box 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 that the form is based on. The ExtendedPrice calculated field in the Order Details Extended query looks as follows:

ExtendedPrice: CCur([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])*100)/100

</li> Change the DefaultView property of the form from Datasheet to Single Form.</li> View the form in Form view. Note that #Error or #Name? is displayed in the Order Subtotal text box in the form footer. Close the form without saving the changes.</li></ol>

<div class="references_section">