Division by zero error on a query

[thorwood]

Hi



I'm new to the forum, and new to Access in general really.

I have the following query, but receive a "division by zero" error if LY_PICKUP is zero. So, I'd like to always return a value of 1 if LY_PICKUP is zero, as it does if it encounters a "null"....but not sure how to do it!

PICKUP: Round(IIf([LY_PICKUP] Is Null,IIf([VAR_PICKUP]<0,-1,1),[VAR_PICKUP]/[LY_PICKUP]),4)

Any assistance is appreciated

Thanks

Tim

[NoellaG]

Hi

try the NZ function:
NZ([myValue],value if myValue is null)

example:
NZ([LY_PICKUP],1)
will return 1 if [LY_PIckup] is null; the value of [LY_PICKUP] if not null

greetings
NG

[thorwood]

Hi Noella

Thanks for the reply, but the only thing is that I don't mind a response of "null". It's a response of "0" that I don't want, because I cannot then divide by zero.

Ideally, I'd want to put something in that implies if [LY_PICKUP] is "null" OR "0" then return "1"..... ?

Thanks

Tim

[NoellaG]

Hi,

have you tried:
iif([LY_PICKUP] = 0,1,NZ([LY_PICKUP],1))
?

greetings
NG