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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

GOS - 14 Aug 2006 12:11 GMT

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

Reply to this Message

John Spencer - 14 Aug 2006 13:11 GMT

Why not just use Sum([Cell]) ?

Sum ignores nulls anyway.

> Hello
>
[quoted text clipped - 9 lines]
>
> Thanks

Reply to this Message

Jeff Boyce - 14 Aug 2006 13:40 GMT

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.

Signature

Regards

Jeff Boyce
Microsoft Office/Access MVP
http://mvp.support.microsoft.com/

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

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

> Hello
>
[quoted text clipped - 8 lines]
>
> Thanks

Reply to this Message

Nick 'The database Guy' - 14 Aug 2006 15:31 GMT

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

Nick

> Hello
>
[quoted text clipped - 8 lines]
>
> Thanks

Reply to this Message