IsError / IIf

[Amy]

I am trying to add an expression to an Access query that
will calculate a rate ($ divided by qty). However, some
of my records have 0 quantity, so I get a #Error as a
result. I am trying to make the rate "0" for records
where the quantity is zero using the following formula:

Val(IIf(IsError([SumOfACQCOST]/[SumOfQTY]),"0",
[SumOfACQCOST]/[SumOfQTY]))

I don't get an error message, but I am still getting the
#Error result in my query and not a zero. Please help!

Thanks.
Amy

 

[Michael Hopwood]

Use:

Iif([denominator]=0,0,[numerator]/[denominator])

Instead of using the IsError() function

 

[Amy]

Thank you, Michael. This works great - I'm embarrassed
not to have thought of it myself!
Amy

-----Original Message-----
Use:

Iif([denominator]=0,0,[numerator]/[denominator])

Instead of using the IsError() function

--
Michael Hopwood

I am trying to add an expression to an Access query that
will calculate a rate ($ divided by qty). However, some
of my records have 0 quantity, so I get a #Error as a
result. I am trying to make the rate "0" for records
where the quantity is zero using the following formula:

Val(IIf(IsError([SumOfACQCOST]/[SumOfQTY]),"0",
[SumOfACQCOST]/[SumOfQTY]))

I don't get an error message, but I am still getting the
#Error result in my query and not a zero. Please help!

Thanks.
Amy

.