Microsoft KB Archive/199355

= ACC2000: Valid Sum Function Returns #Error? in Calculated CTRL =

Article ID: 199355

Article Last Modified on 6/28/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q199355



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



SYMPTOMS
If you open a form in Form view and have two or more calculated controls, you may receive an "#Error?" error in all of the calculated controls on the form, even if the expression for an individual control is valid.



CAUSE
All of the domain functions are based on the same query (over the underlying recordset). If one of the bound functions on the form has a binding error, all of the functions on the form will return an error.



RESOLUTION
Find the control or controls that use an invalid expression. Then, either remove the control or correct the expression that you use for the control.



MORE INFORMATION
You can use aggregate (totals) functions to calculate results only for fields, not for controls. Examples of aggregate functions are Sum, Avg, and Count.

Steps to Reproduce Behavior
 Create a new form based on the Orders table from the sample database Northwind.mdb.  Add a text box to the form bound to the Freight field of this table. Set the following properties:   Text Box --  ControlName: Freight ControlSource: Freight   Add an unbound text box to the form. Set the following properties:   Text Box -  ControlName: Test1 ControlSource: =Sum([Freight])   Add another text box to the form. Set the following properties: <pre class="fixed_text">  Text Box ControlName: Test2. ControlSource: =Sum([Test1]) </li> View the form in Form view. Note that #Error? appears in the second and third text boxes (Test1 and Test2). Because you cannot use the Sum function on a calculated expression, Test2 is invalid. This causes all calculated expressions on the form to return "#Error?."</li></ol>

If you remove Test2 from the form, Test1 will display the correct value.

Additional query words: poundname prb

Keywords: kberrmsg kbprb kbusage KB199355

-

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

© Microsoft Corporation. All rights reserved.