compare as percentage difference two zeros cause error

[Dave_epic]

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?

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

Cheers

 

[vbaInet]

SELECT  ((fieldA -  fieldB )/ iif(fieldB = 0, Null, fieldB) * 100 AS percentdiff 
FROM mytable;

 

[Dave_epic]

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.

SELECT  ((fieldA -  fieldB )/ iif(fieldB = 0, "1" , fieldB)) * 100 AS percentdiff 
FROM mytable;

 

[vbaInet]

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.