ACC2000: You Cannot Sum Calculated Controls in Forms or Reports

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.

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.

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: with the calculation placed inside of the Sum function as follows: 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:

1. In the sample database Northwind.mdb or in the sample project NorthwindCS.adp, open the Order Details Extended query or view in Design view.
2. 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.
3. Close the query and open the Orders Subform form in Design view.
4. Note that the Order Subtotal text box in the form footer has the following expression:
   =Sum([ExtendedPrice])
5. 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.

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.

1. Open the sample database Northwind.mdb.
2. Open the Orders Subform form in Design view.
3. Add a text box to the detail section, and then set the following properties:
   Name: My Extended Price
   ControlSource: =[UnitPrice] * [Quantity]
   
4. 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
5. Change the DefaultView property of the form from Datasheet to Single Form.
6. 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.

For more information about using calculated controls in forms and reports, click Microsoft Access Help on the Help menu, type Calculated Controls in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.