By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,659 Members | 1,945 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,659 IT Pros & Developers. It's quick & easy.

Summing values in a form inc NULL VALUES?

P: 50
Hi guys hope you can help

I have a text box for each day of the week in a form and numbers are entered into them (but sometimes they need to be left blank i.e. not 0). I am summing these values for a total in the footer and get an error (it works if there is a number in each txt box but I some need to be left blank)....

I have tried the control source of the total in the footer to be...

=Sum([StdMon]+[StdTue]+[StdWed]+[StdThu]+[StdFri]+[StdSat]+[StdSun])

...this gives an error.

=[StdMon]+[StdTue]+[StdWed]+[StdThu]+[StdFri]+[StdSat]+[StdSun]

...this works if a number is in each text box (not possible)

OllyJ
Apr 30 '08 #1
Share this Question
Share on Google+
3 Replies


rsmccli
P: 52
Hi guys hope you can help

I have a text box for each day of the week in a form and numbers are entered into them (but sometimes they need to be left blank i.e. not 0). I am summing these values for a total in the footer and get an error (it works if there is a number in each txt box but I some need to be left blank)....

I have tried the control source of the total in the footer to be...

=Sum([StdMon]+[StdTue]+[StdWed]+[StdThu]+[StdFri]+[StdSat]+[StdSun])

...this gives an error.

=[StdMon]+[StdTue]+[StdWed]+[StdThu]+[StdFri]+[StdSat]+[StdSun]

...this works if a number is in each text box (not possible)

OllyJ
I think you could get this to work by converting any values that may be null to 0 like

Nz([StdFri], 0)+Nz([StdSat], 0)+Nz([StdSun], 0) etc.

so if any of the values happen to be null they will be converted to 0, otherwise they will be left alone.

hth
rsmccli
Apr 30 '08 #2

P: 50
worked a treat thanks
Apr 30 '08 #3

NeoPa
Expert Mod 15k+
P: 31,487
Indeed it does :) Nice answer.

You may even get away with the version omitting the default val.
Expand|Select|Wrap|Line Numbers
  1. Nz([StdFri]) + Nz([StdSat]) + Nz([StdSun]) etc.
May 1 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.