CODEWITHSUNDEEP
ms-accessms-access-2007
Rister
Rister

Reputation: 99

SUM() on a form footer resulting in #Error

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.

Upvotes: 4

Views: 35077

Answers (6)

Edward Lee
Edward Lee

Reputation: 1

None of the Sum() formulas will work in the text boxes that are copied from the Detail section into the form footer, even with the simplest of expressions, like:

=Sum([Hours])

The text boxes that use the Sum() formula in the form footer have to be individually added to the form footer from the Form Design menu instead of copied from the Detail section and modified to contain Sum() formulas. This is a bug of Microsoft Access. The version in my File / Account menu is, "Version 2108 (Build 14326.21018 Click-to-Run)".

Upvotes: 0

SonOfAGun
SonOfAGun

Reputation: 31

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.

Upvotes: 3

JeffO
JeffO

Reputation: 8043

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.

Upvotes: 1

David O'Donoghue
David O'Donoghue

Reputation: 61

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

Upvotes: 6

michaelf
michaelf

Reputation: 499

I know you said "Form" but for those who have issues with the Sum formula in an Access "Report" the formula has to be in the Report Footer NOT the Page footer. Took me a while to figure it out as Access defaults to only showing the page footer.

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

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.

Upvotes: 2

Related Questions