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


The information in this article applies to:

  • Microsoft Access 2000
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 '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.
Modification Type:MajorLast Reviewed:6/28/2004
Keywords:KbClientServer kbprb KB225992
©2004 Microsoft Corporation. All rights reserved.