compare as percentage difference two zeros cause error

Dave_epic

Registered User.
Local time
Today, 13:41
Joined
Mar 6, 2008
Messages
39
Hi I have a simple query below to find the percentage difference between 2 long integer columns. However I find that if any of the 2 values in a row are both zero it causes an error in the result for this record and if I run it from a VBA module it causes an overflow error and stops the routine. How do i get around this?


Code:
SELECT  ((fieldA -  fieldB [B])/[/B] fieldB) * 100 AS percentdiff 
 
FROM mytable;

Cheers
 
Code:
SELECT  ((fieldA -  fieldB )/ iif(fieldB = 0, Null, fieldB) * 100 AS percentdiff 
FROM mytable;
 
Thanks, Thats great. It has the same function as NULLIFF in transact sql.
I also found that if I changed the null to 1 then I get a 100% increase if it
increases from 0 to 1 for example.

Code:
SELECT  ((fieldA -  fieldB )/ iif(fieldB = 0, "1" , fieldB)) * 100 AS percentdiff 
FROM mytable;
 
Thanks, Thats great. It has the same function as NULLIFF in transact sql.
Yes, with what I wrote they are essentially the same.

I also found that if I changed the null to 1 then I get a 100% increase if it
increases from 0 to 1 for example.
If this is the result you want then that's fine. However, I put Null because 100% is not a true representation of the percentage when the denominator is zero.
 

Users who are viewing this thread

Back
Top Bottom