Microsoft KB Archive/225992

= ACC2000: Cannot Calculate an Expression Inside an Aggregate Function on a Form or Report in an Access Project =

Article ID: 225992

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q225992



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

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
In a Microsoft Access project, if you place a calculation inside an aggregate (totals) function, you may see one of the following problems:  When you open a form, #Error is displayed in a text box.

-or-

 You may see the following error when you try to preview or print a report:

The expression 'The expression ' ' is invalid. @Aggregate functions are only allowed on output fields of the Record Source. @1@.

1' is invalid.

Aggregate functions are only allowed on output fields of the Record Source.





CAUSE
This problem may occur when you set the ControlSource property of a text box on a form or a report to a calculated value inside an aggregate function. For example:

=Sum([Quantity]*[UnitPrice])



RESOLUTION
Create a field in the record source of the form or the report based on the expression, and then use the aggregate function on that field in the form or the report. For example, create a new field named TotalPrice in the Order Details Extended view. The expression to calculate TotalPrice is:

[Order Details].[Quantity]*[Order Details].[UnitPrice]

Then add a text box to the form's footer, and set the ControlSource property to =Sum([TotalPrice]).



MORE INFORMATION
Aggregate functions include Avg, Count, Max, Min, and Sum.

Steps to Reproduce Behavior

 * 1) Open the sample Access project NorthwindCS.adp.
 * 2) Open the Orders Subform in Design view.
 * 3) In the form footer section, add an unbound text box, and set its ControlSource property to =Sum([Quantity]*[UnitPrice]).
 * 4) Set the DefaultView property of the form to Single Form.
 * 5) View the form in Form view.
 * 6) Close the form without saving the changes.
 * 7) Select the Order Details Extended view, and then on the Insert menu, click AutoReport.
 * 8) Switch to Design view.
 * 9) In the report footer section, add an unbound text box, and set its ControlSource property to =Sum([Quantity]*[UnitPrice]).
 * 10) On the File menu, click Print Preview.

Additional query words: prb

Keywords: kbprb kbclientserver KB225992

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.