When using NZ(cell),0)*1 and try to sum it changes to expression?

[GOS]

Hello

I am using the formula NZ(cell),0)*1 as I need to poipulate any null values
with zero.
I then need to sum this so I choose Group by and then sum. This keeps
defaulting to Expression and the Formula changes to SUM(NZ(cell),0)*1).
The results are also multiplied by 10 even though all the properties look
correct.

Can anyone help.

Thanks

 

[John Spencer]

Why not just use Sum([Cell]) ?

Sum ignores nulls anyway.

 

[Jeff Boyce]

Nz(cell,0) evaluates to 0 when "cell" contains null. Multiplying 0 times 1
will always be 0. The sum of any number of 0's is 0.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/

 

[Nick 'The database Guy']

Hi Gos,

Have you not given us the whole formula, there are an uneven number of
parenthesis (brackets).

It should read NZ(cell,0)*1 or NZ([cell],0)*1

Good luck