Division by zero error

[pazgb]

i am also having problems getting division by zero. It might have something to do with null values in my table. How can I check to see if null in my table but when i check i need it to be in a query. Like some kind of Iff command in my SQL, if null set to 1 because i am dividing by the value return by the query to get the percentage

PTDRetailTissuePackPer: iff([qry_PTDRetailTisPackCon]![NumRetailTisPackCon]=0,1,(1-([qry_PTDRetailTisPackNonCon]![NumRetailTisPackNonCon]/[qry_PTDRetailTisPackCon]![NumRetailTisPackCon])))

if i try using that i get a different error:
undefined function iff in expression

 

[scriverb]

The NZ function can be used to do what you want.

NZ([field name], 1)

This will return the value 1 if the [field name] is null.

As for the divide by zero this is a different situation. You can use an IIF command to test for zero(0) and return a substitute value like one(1). This is important to do in the event of a divide by zero error. Your example is a good example of how to handle this situation. Your program should be aware that the one(1) value for this query field means something different that if the division actually returned a value of 1. So, you might want to put in a value of 999 and eliminate them from any rollup figures.

PTDRetailTissuePackPer: iff([qry_PTDRetailTisPackCon]![NumRetailTisPackCon]=0,999,(1-([qry_PTDRetailTisPackNonCon]![NumRetailTisPackNonCon]/[qry_PTDRetailTisPackCon]![NumRetailTisPackCon])))

Please post back with any questions.




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

You might also just return a zero(0) value in your column. This way any totals that you rollup won't be affected by this record.

PTDRetailTissuePackPer: iff([qry_PTDRetailTisPackCon]![NumRetailTisPackCon]=0,0,(1-([qry_PTDRetailTisPackNonCon]![NumRetailTisPackNonCon]/[qry_PTDRetailTisPackCon]![NumRetailTisPackCon])))




Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pazgb]

Thank you bob for helping me out, i used


PTDRetailTissuePackPer: iff([qry_PTDRetailTisPackCon]![NumRetailTisPackCon]=0,999,(1-([qry_PTDRetailTisPackNonCon]![NumRetailTisPackNonCon]/[qry_PTDRetailTisPackCon]![NumRetailTisPackCon])))

but i get an error saying: undefined function iff in expression

any ideas?

 

[scriverb]

It is supposed to be IIF rather than iff. Just change that and it will work.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[pazgb]

Thank you so much Bob, you helped so much. Now I can go to lunch