divide by zero

[rbee]

Hi,
I am pretty new to access in general so forgive me because I have seen this posted elsewhere but do not know how to apply it to my situation.

I am essentially trying to do a-b/b, however b can be null or zero.

round(abs(((im.metric_val-i.bid)/i.bid)*100),3) AS Abs_Pct_Diff

if i.bid is null or zero I'm okay with having it return 0.

I have seen several suggestions to try nz or IIF statements but I cannot write them properly given my query.

Any help is appreciated,
Thanks!

[RuralGuy]

How about a compound IIF? WARNING - UNTESTED
IIF bid = 0, 0, IIF IsNull(bid),0,round(abs(((im.metric_val-i.bid)/i.bid)*100),3) AS Abs_Pct_Diff

[rbee]

As written I get this error:

IIF function without () in query expression 'IIF bid='

I've tried adding parenthesis in a varity of ways but I cannot seem to find the right place for them.

[rbee]

This ran, still getting the divide by 0 error.

IIF(i.bid = '0', 0), IIF(IsNull(i.bid),0),round(abs(((im.metric_val-i.bid)/i.bid)*100),3) AS Abs_Pct_Diff,

[RuralGuy]

I'm thinking that an Immediate IF (IIF) may test all of the conditions but maybe not. Is your field [bid] numeric? If so then IIF(i.bid = '0', 0) should be IIF(i.bid = 0, 0)!

[RayMilhon]

My suggestion assuming i.bid is numeric.

IIF(i.bid = 0 Or isnull(i.bid), 0, round(abs(((im.metric_val-i.bid)/i.bid)*100),3)) AS Abs_Pct_Diff

[rbee]

bid data type is integer. Removed the quotes.. still getting the division by zero error.

I am thinking, maybe, the tables I am trying to join do not always have matches so when that happens it causes the division by zero error. So if I take the ID from the first table, it does not find a match in the second table, and tries to divide by zero.

[RuralGuy]

You may need to use a User Defined Function (UDF) here.

[JoeM]

I am thinking, maybe, the tables I am trying to join do not always have matches so when that happens it causes the division by zero error. So if I take the ID from the first table, it does not find a match in the second table, and tries to divide by zero.

I recreated your scenario, and this seemed to work well for me.

Abs_Pct_Diff: IIf(IsNull(i.bid),0,Round(Abs(((im.metric_val-i.bid)/i.bid)*100),3))

[rbee]

No actually, there are 0's entered for [bid].

I cannot figure out an IF or nz statement to bypass them.. nothing I am trying is working.

[rbee]

Trying this now.

[JoeM]

Sorry, I missed the fact that you have Nulls and Zeroes to contend with. I added some zeroes, and this seemed to handle both cases:

Abs_Pct_Diff: IIf(nz(i.bid,0)=0,0,Round(Abs(((im.metric_val-i.bid)/i.bid)*100),3))