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

Is the field "WeldInches" existing in the data source for this form?
What datatype the field "WeldInches" is?

EDIT: I have looked at all your comments. If it doesn't work by databinding, try and use the unbounded way. At runtime, get the value of WeldInches using DSUM and set the footer textbox's value when the form loads.

Also, remember to update it at places where you think the SUM could change.

Answer 2

You want to sum by the name of the column in the record source: SUM([WeldInches])

Make sure there are no other textboxes with the name WeldInches.

Answer 3

I have the same problem. While all worked fine with Sum in form footers, for one reason I don't know, it doesn't work and it drives my crazy! I googled a lot, but didn't find the answer yet.

Answer 4

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))