Calulated query - null values (1 Viewer)

[DavidCon]

Hi All,

I'm trying to create a calculated query (nothing fancy) but I'm having an issue with null values. When I run the query if omits results as it is trying to calculate against a null field.

I have tried to add nz() to the calculations but I must be getting this wrong.

Could you please review my code and advise where I am going wrong?

UNIT PRICE VARIANCE (NET): Nz([P/S CURRENT UNIT NET PRICE]-Nz([NEW UNIT PRICE (NET)]))

Without Nz()

[P/S CURRENT UNIT NET PRICE]-[NEW UNIT PRICE (NET)]

NEW UNIT PRICE (NET): Nz([NEW PACK PRICE (NET)]/Nz([NEW PACK SIZE]))

Without Nz()

NEW UNIT PRICE (NET): [NEW PACK PRICE (NET)]/[NEW PACK SIZE]

NEW PACK PRICE (NET): Nz([Tot Prc])-Nz([Tot Prc]*(Nz([Discount]/100)))[CODE]

Without Nz()
[CODE]NEW PACK PRICE (NET): [Tot Prc])-[Tot Prc]*([Discount]/100)[CODE]

 

[cyanidem]

With Nz() function you need to specify "value if null" parameter so it returns i.e. 0 (zero) when a value is null.
So instead of:
Nz([Tot Prc])
use:
Nz([Tot Prc],0)
Also it's not recommended to use spaces and special characters in field names, like your [NEW UNIT PRICE (NET)], that's asking for trouble.

 

[sneuberg]

I wonder about the parenthesis locations. Don't you mean

UNIT PRICE VARIANCE (NET): Nz([P/S CURRENT UNIT NET PRICE])-Nz([NEW UNIT PRICE (NET)])

and

NEW UNIT PRICE (NET): Nz([NEW PACK PRICE (NET)])/Nz([NEW PACK SIZE])

but in the second case you need to guard against division by zero so maybe

NEW UNIT PRICE (NET): Nz([NEW PACK PRICE (NET)])/Nz([NEW PACK SIZE],1)

 

[sneuberg]

With Nz() function you need to specify "value if null" parameter so it returns i.e. 0 (zero) when a value is null.
.

That depends. https://support.office.com/en-us/article/Nz-Function-8ef85549-cc9c-438b-860a-7fd9f4c69b6c says

If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression), depending on whether the context indicates the value should be a number or a string.