Calclating totals - Sum fails on empty column - Rookie

Andy · Feb 17, 2004

Try the Null to Zero function:
nz([MyVal1],0) + nz([MyVal2],0)

hth,
Andy

Atlas · Feb 17, 2004

I have a simple form in continuos view. I have placed a textbox in the
form's footer to calculate totals of 2 "money" column (SQL Server), with the
control code:

=Sum([MYVAL1]) + Sum([MYVAL2])

If at least one value is set in both the columns the function works. If one
of the two columns has no values the function fails. It doesn't display any
"#error" in the text box, it simply blanks the contenent.

Example:

Row MYVAL1 MYVAL2
1 10
2
3 20

Works and returns: 30

Row MYVAL1 MYVAL2
1
2 50
3

Doesn't work and returns nothing (empty)

Any hint?

Atlas · Feb 17, 2004

Try the Null to Zero function:

nz([MyVal1],0) + nz([MyVal2],0)

I did, I get an "#error"

Rod Scoullar · Feb 18, 2004

Atlas,

Adding a Null to anything produces a Null as a result. This means one of
your totals remains Null.

Try Nz(Sum([MyVal1),0) + Nz(Sum([MyVal2]),0)

Rod Scoullar.

Atlas · Feb 18, 2004

Try Nz(Sum([MyVal1),0) + Nz(Sum([MyVal2]),0)

Rod now it works fine!

Thanks (rookie improving....)