handling division by zero in access

[miconian]

I have this field in query design:

PercentDif: [Difference]/[SumOfNet Amount]

Sometimes the denominator is zero, causing the result to be #error. How can I avoid this? Having it show up as zero would be fine.

 

[revans]

There is probably an "elegant" way of doing this but I had written a function to do this for me a while back. Off the top of my head it was simply like this:

Function DivideByZero(numerator as long, denominator as long) as single
dim temp as single
if denominator = 0 then
  temp=0
else
  temp=numerator/denominator
end if
DivideByZero=temp
end Function

Then call it in your query

PercentDif: DivideByZero([Difference],[SumOfNet Amount])

Or something....

hth,

Rich

 

[Joe4]

The nice thing about a UDF is you can re-use it over and over without too much effort.

However, if you are looking for a straight calculation without the use of a UDF, it might look something like this:

PercentDif: IIF([SumOfNet Amount]<>0,[Difference]/[SumOfNet Amount],0)