Summing values in a form inc NULL VALUES?

[OllyJ]

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

[rsmccli]

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

[OllyJ]

worked a treat thanks

[NeoPa]

Indeed it does :) Nice answer.

You may even get away with the version omitting the default val.

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