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

Last Reviewed: April 3, 1999