Thread: Sum in form footer (Access 2003)

Hello again. Working with Access 2003.

I'm trying to put a running Sum into a Form Footer. I put the text box in the Form Footer and created this Expression in the Control Source:

Code:

=Sum([H0310]+[H0615]+[H0715]+[H1014]+[H1314]+[SS0312]+[SS0317]+[SS0619]+[SS0712]+[SS1012])

Where H0310, H0615, etc...are the Names of the fields.

I'm getting a #Error in the new text box.

The format of each fields control source is Number and the format of the new text box is General Number.

Any ideas?
Thanks!

Hi WillysK5,

Try removing the Sum function so the control source looks like this:

Code:

=[H0310]+[H0615]+[H0715]+[H1014]+[H1314]+[SS0312]+[SS0317]+[SS0619]+[SS0712]+[SS1012]

Cheers,

Is the form looking at an existing record (not a new record) with data in those fields?

Let me start with this is total speculation. There is no knowledge behind what I am about to say. If it sees the fields as being empty or null it won't give them a 0 value thus resulting in a Err or blank. Total guess, so take that for what it is worth, I would try the following.

Code:

=Nz([H0310],0)+Nz([H0615],0)+Nz([H0715],0)+Nz([H1014],0)+Nz([H1314],0)+Nz([SS0312],0)+Nz([SS0317],0)+Nz([SS0619],0)+Nz([SS0712],0)+Nz([SS1012],0)

Hope this helps,

Dan

Hi WillysK5,

I built a mock-up to test the formula, and it works fine, but that isn't helping here. The only way I can reproduce this error is by disconnecting the Record Source for the form (or by removing one of the Record Source fields).

Probably a dumb question, but do you still have a Record Source connected to the form?

Cheers,

Originally Posted by ConneXionLost

Hi WillysK5,

I built a mock-up to test the formula, and it works fine, but that isn't helping here. The only way I can reproduce this error is by disconnecting the Record Source for the form (or by removing one of the Record Source fields).

Probably a dumb question, but do you still have a Record Source connected to the form?

Cheers,

I did a similar, however I got a blank when one of the fields was null, I may have screwed up the syntax. I am gonna try another thing quick.

Hi WillysK5,

Okay, really grasping at straws now; any chance you used Captions in your table and are using them instead of the Field Names in the formula?

Note - I'm running out of guesses!

Cheers,

Code:

=Nz([H0310])+Nz([H0615])+Nz([H0715])+Nz([H1014])+Nz([H1314])+Nz([SS0312])+Nz([SS0317])+Nz([SS0619])+Nz([SS0712])+Nz([SS1012])

Try that,

I just checked it with mine, no syntax error, unless one of the field names if off on yours.

Great!

So in your table, you might want to set the default values for those fields to zero "0" instead of blank.

Cheers,