Nz function and link to Excel

[thart21]

Hi there, can't seem to find a solution to this seemingly easy problem anywhere. Since Excel does not recognize the Nz function, how would I re-write this in my query grid,



Fixed: Nz([Fixed Qty],0)+Nz([Schl Del Qty],0)

I've tried to change it to:
Sum(Iif(IsNull([Fixed Qty],0))+(Iif(IsNull([Schl Del Qty],0)))))

Which gives me a "wrong number of arguments" error.

Am I close?? Thanks for any help,

Toni

[boblarson]

If the query is in Access then the NZ function should work. If you are doing the query from MS Query then you would have to do it the second way but you are only supplying a value for the True side - you also need to supply the False side:

Sum(Iif(IsNull([Fixed Qty],0,[Fixed Qty]))+(Iif(IsNull([Schl Del Qty],0,[Schl Del Qty])))))

[thart21]

Hey Bob, thanks for the reply, what happened was, I already have my Access query linked to Excel and it's been working great, when I added the field with the Nz function and went to refresh my link in Excel, I got the Nz undefined function error.
I just tried your suggestion and got the "wrong # of arguments error" but I understand what you are saying and will play around with it. Thanks so much!

Toni

[thart21]

I give up, I've tried changing location of ")", "0" and other fields, but continue to get the same error, any ideas on what might be missing?

Thanks!

Toni

[boblarson]

Sorry, I didn't look carefully enough at the parens. This should work:

Sum(IIf(IsNull([Fixed Qty]), 0, [Fixed Qty]) + IIf(IsNull([Schl Del Qty]), 0, [Schl Del Qty]))

[thart21]

Thanks Bob, appreciate the help!