[SOLVED] Sum in form footer (Access 2003)

WillysK5 · #1 05-22-2009, 02:02 PM

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

WillysK5 · #2 05-26-2009, 11:46 AM

Bumpity bump bump

Any help would be greatly appreciated!

ConneXionLost · #3 05-26-2009, 12:22 PM

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]

WillysK5 · #4 05-26-2009, 12:45 PM

Thanks Conne, but it just leaves it blank now. There's no #Error, but there's no total either.

WillysK5

ConneXionLost · #5 05-26-2009, 01:14 PM

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

WillysK5 · #6 05-26-2009, 01:23 PM

Quote:

Originally Posted by ConneXionLost

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

Both. The main function is as a running total in a new form, but it doesn't do that or show up in an existing record.

And yes, there are existing records for it to look at...

split_atom18 · #7 05-26-2009, 03:27 PM

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)

WillysK5 · #8 05-26-2009, 03:34 PM

Thanks Dan, but that gives me an invalid syntax message.

I just wish I knew more about Access expressions.....

ConneXionLost · #9 05-26-2009, 03:43 PM

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,

WillysK5 · #10 05-26-2009, 03:55 PM

Yeah. I have one table where all the data from that one form is stored. It's named in the Record Source of that form.

split_atom18 · #11 05-26-2009, 04:06 PM

Quote:

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.

ConneXionLost · #12 05-26-2009, 04:11 PM

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,

split_atom18 · #13 05-26-2009, 04:25 PM

Code:

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

WillysK5 · #14 05-26-2009, 04:29 PM

Woohoo! That's it! I forgot a parenthesis.

Thanks!!

ConneXionLost · #15 05-26-2009, 04:38 PM

Great!

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

Cheers,

Welcome to the Excel Forum

Forum Statistics