Calculations when values can be zero

Novaember

Registered User.
Local time
Today, 11:26
Joined
Nov 3, 2009
Messages
33
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
 
What about something like this
AccuracyRate: IIf([totalItems]=0,0,(1-([TotalIncorrectItems]/[totalItems])))
-JP
 
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]))
 
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)
 
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!
 

Users who are viewing this thread

Back
Top Bottom