Summing Null Fields

[Guest]

I have a table with many currency fields that need to be summed.
Only a random select few (7-8) of them will be populated in any given record
and when I run the querry to sum I get nothing as a result.
I isolated to whenever a null field goes into the sum is throws the entire
calc off.
Is there any way around this beside setting the default value to zero
because a zero could get erased accidentally.

 

[Rick B]

I have not tested this, but try something like...

=Sum(Nz([YourFieldName],0))

This replaces Nulls with "0".

 

[Michel Walsh]

Hi,


SELECT Nz(f1, 0) + Nz(f2, 0) + Nz(f3, 0) + Nz(f4,0) + Nz(f5, 0) + Nz(f6,
0) + Nz(f7, 0) + Nz(f8, 0) As HorizontalSum FROM myTable


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

That was it! Thank you sir.

I have not tested this, but try something like...

=Sum(Nz([YourFieldName],0))

This replaces Nulls with "0".

--
Rick B

I have a table with many currency fields that need to be summed.
Only a random select few (7-8) of them will be populated in any given record
and when I run the querry to sum I get nothing as a result.
I isolated to whenever a null field goes into the sum is throws the entire
calc off.
Is there any way around this beside setting the default value to zero
because a zero could get erased accidentally.

 

[Guest]

Thank you so much Michel! That was the answer!

Hi,


SELECT Nz(f1, 0) + Nz(f2, 0) + Nz(f3, 0) + Nz(f4,0) + Nz(f5, 0) + Nz(f6,
0) + Nz(f7, 0) + Nz(f8, 0) As HorizontalSum FROM myTable


Hoping it may help,
Vanderghast, Access MVP