Calculations when values can be zero

[Novaember]

Hello
I have some queries that run and total items.
Then when I get those items I calculate an accuracy rate and a defect rate.

The formula is:
AccuracyRate: 1-([TotalIncorrectItems]/[totalItems])

However I run into a problem when TotalIncorrectItems and totalItems are both zero.

What do I need to put around this when these two things are both zero?
Thanks
Happy Tuesday!
Nova

 

[JPearson]

What about something like this
AccuracyRate: IIf([totalItems]=0,0,(1-([TotalIncorrectItems]/[totalItems])))
-JP

 

[vbaInet]

The problem lies with the divisor, [totalItems]. If you divide any number by 0 you will get an error and the IIF function evaluates both sides (i.e. the True and False parts).

Use the IIF() and Nz() functions:

http://www.techonthenet.com/access/functions/advanced/iif.php
http://www.techonthenet.com/access/functions/advanced/nz.php

Maybe something like this:

1-(iif(nz([totalItems], 0), 0, [TotalIncorrectItems])/iif(nz([totalItems], 0) = 0, 1, [totalItems]))

 

[DCrake]

Are you talking about zero or null values. If null you need to use Nz() around each value. However 0/0 my give you division by zero error. Therefore you need to cheach if either value is greater then zero.

x = iif(y>0 and z>0,1-(y/z),0)

 

[Novaember]

What about something like this
AccuracyRate: IIf([totalItems]=0,0,(1-([TotalIncorrectItems]/[totalItems])))
-JP

Brilliant Worked like a charm!
Thanks so much!
Nova

Also I have the Nz on the other queries that give me totalItems and TotalIncorrectItems. So if those are null it delivers a zero to me.

Thanks so much!