ACC: Valid Sum() Function Returns #Name? in Calculated Control

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

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

CAUSE

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

RESOLUTION

Find the control or controls that use an invalid expression. Either remove the controls or correct the expression you use for the controls.

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

1. Create a new form based on the Orders table from the sample database Northwind.mdb (or NWIND.MDB in 1.x and 2.0).

2. 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
   

3. Add an unbound text box to the form. Set the following properties:

         Text Box
         -----------------------------
         ControlName: Test1
         ControlSource: =Sum([Freight])
   

4. Add another text box to the form. Set the following properties:

         Text Box
         ----------------------------
         ControlName: Test2.
         ControlSource: =Sum([Test1])
   

5. View the form in Form view. Note that #Name? 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 "#Name?."