SUM() on a form footer resulting in #Error

Question

I'm trying to display the sum of a field in a text box in the form footer. The field is not calculated in any way.

Here are a couple of the things I've tried:

=Sum([txtWeldInches])
=Sum([WeldInches])
=Sum(CDbl([txtWeldInches]))
=Sum(CDbl([WeldInches]))

...well you get the idea. Each iteration I've used results in the Text Box displaying #Error Without exception.

I've used similar constructs in different forms in the same project, so I'm not sure what the problem might be.

Has anyone run into this before?

EDIT: I ended up writing a VBA routine to update the boxes when it was likely that they would be changed rather than trying to get a bound sum() function to work.

Answer 1

http://support.microsoft.com/kb/199355

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.

In other words, make sure all your footer controls are resolving properly and not hitting any nulls.

If you use a SUM or AVG then make sure you are also using the Nz function:

ControlSource = =SUM(NZ([FIELD],0))

Answer 2

I had the same problem as Rister. The source of the form was an underlying query. I had a bound text box named txtQty on this form. Its control source was Qty (based on the underlying query of the form). I created an unbound text box and entered =SUM([txtQty]) and received an error. I tried various ways to find a solution and was very desperate. Then I deleted the underlying query and created a new one using the same name and fields as before. Then I entered =SUM([Qty]) into the unbound text box on the form and voila, it worked. Note that I didn't enter the name of the bound text box (txtQty) into the expression, but its control source (Qty). I don't know why, but it worked for me.