IsError / IIf

  • Thread starter Thread starter Amy
  • Start date Start date
A

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
 
Use:

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

Instead of using the IsError() function
 
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
Click to expand...


.
Click to expand...
 
Back
Top