Article ID: 207763 - View products that this article applies to.
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).
When you open a form or a report, you may see the following behavior:
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 ExpressionPlace 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 ViewIf 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:
Steps to Reproduce the BehaviorCAUTION: 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.
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.