division by zero error but it works when I dont have any criteria

[jason2885]

I have made a query with to take the percent difference of two mileages and then if they are greater than 2.5% to put a 1 or if not a 0. I try and put a 1 in criteria in order to only see the ones that are changed by greater than 2.5% and I get the divide by zero error. Any ideas how to fix this problem.

Here is my IFF statement

diff: IIf((Abs(([Mile1]-[Mile2]))/[Mile1]>=0.025),1,0)

 

[Brianwarnock]

Ithink you have a ) in the wrong place, but don't know if that solves the problem.
diff: IIf((Abs(([Mile1]-[Mile2]))/[Mile1])>=0.025,1,0)
Simplified I think it should be
diff: IIf(Abs([Mile1]-[Mile2]/[Mile1])>=0.025,1,0)

Brian

Edit no the simplified version is incorrect,sorry

 

[jason2885]

Ithink you have a ) in the wrong place, but don't know if that solves the problem.
diff: IIf((Abs(([Mile1]-[Mile2]))/[Mile1])>=0.025,1,0)
Simplified I think it should be
diff: IIf(Abs([Mile1]-[Mile2]/[Mile1])>=0.025,1,0)

Brian

Didnt help, some of the time both of the mileages are 0 which causes them to go to undefined. I wonder if thats the problem, but the strange thing is if I dont have any criteria it shows either a 1 a 0 or a #error.

I need to put 1 in the criteria so it will show all the ones that changed mileage.

 

[Brianwarnock]

What do you want to happen if either field is 0?

Brian

 

[jason2885]

What do you want to happen if either field is 0?

Brian

a 0 would be fine if both of the fields are 0. I just need to get the ones that change by 2.5% or more seperated from the rest. Im trying to write an IFF statement for that right now but I think I did something wrong.

This is what i have so far


diff: IIF([Mile1]=[Mile2]=0,0,IIf((Abs([Mile1]-[Mile2])/[Mile1]>=0.025),1,0))

 

[Brianwarnock]

a 0 would be fine if both of the fields are 0. .

Yes but what if only 1 of them can be 0, is mile2 always > mile1.

Brian

 

[Brianwarnock]

If mile1 and mile2 can only be 0 together then select mile1 in the query with a criteria>0, then those records will not be included.

Brian

 

[jason2885]

Yes but what if only 1 of them can be 0, is mile2 always > mile1.

Brian

Nope they can go either way. They can decrease in mileage or increase in mileage. Also there are some cases when the things have 0 miles now and 0 miles back then.

There is also a possiblity of have 6miles before and 0 now.

I hope this makes sense. One of them is allowed to be 0. Ohh I see what you mean.

 

[Brianwarnock]

So the question is if 1 is 0 and the other not is that a 1 as it is an infinite change, or a 0 because 1 was 0, which ever way you must allow for this. I sense a function being required, but I could be wrong.

Brian

 

[Dennisk]

you need to include a test to see if the divisor is zero

IIF([Mile1]>0,IIf((Abs(([Mile1]-[Mile2]))/[Mile1])>=0.025,1,0),0)

 

[jason2885]

Thanks that did the trick. I dont know why I didnt think of that sooner.

 

[Brianwarnock]

But what if mile1 is 0 and mile2 is 6, that is a change greater than your criteria but you are going to flag it as a 0, hence my questions.

Brian

 

[jason2885]

But what if mile1 is 0 and mile2 is 6, that is a change greater than your criteria but you are going to flag it as a 0, hence my questions.

Brian

Ahh yes I see what you are saying that doesnt work all the time.

 

[Brianwarnock]

Try this

IIf([mile1]<>0 And [mile2]<>0,IIf(Abs([mile1]-[mile2])/[mile1]>0.25,1,0),IIf([mile1]>0 Or [mile2]>0,1,0))

Brian

 

[jason2885]

Try this

IIf([mile1]<>0 And [mile2]<>0,IIf(Abs([mile1]-[mile2])/[mile1]>0.25,1,0),IIf([mile1]>0 Or [mile2]>0,1,0))

Brian

That worked but Im going to have to sit down and read through what that expression says. I didnt know you could do that.

 

[Brianwarnock]

If it helps
it checks to see if neither are zero, if true it does your original IIF
if false it checks to see if either is not zero, note both cannot be as it would have returned true before, and if so returns a 1 else a 0 as both must be zero.

Brian