424,830 Members | 1,013 Online
Need help? Post your question and get tips & solutions from a community of 424,830 IT Pros & Developers. It's quick & easy.

# Query "Division by Zero" problem

 100+ P: 120 Expand|Select|Wrap|Line Numbers Scrap%: IIf([BadParts]=0,0,Nz([BadParts]/[GoodParts])) My issue is when I select Ascending or add some sort of filter in design view, then run it I get a pop up msgbox that says "Division By Zero". I need to build reports of of this query with different filters and cannot get them to run because of this. Please help. Thank you so very much in advance for any help I recieve. Jul 18 '18 #1

It's the good parts that can't be 0, not bad parts.

Edit: But also, are you trying to calculate percentage? Or ratio? If you're trying to calculate percentage, it should be bad parts divided by the sum of bad parts and good parts.

5 Replies

 Expert Mod 10K+ P: 12,335 It's the good parts that can't be 0, not bad parts. Edit: But also, are you trying to calculate percentage? Or ratio? If you're trying to calculate percentage, it should be bad parts divided by the sum of bad parts and good parts. Jul 18 '18 #2

 Expert 100+ P: 1,430 Hi Kevin I thought we had dealt with this some months ago. As Rabbit says you must ensure that GoodParts is not Null or Zero Expand|Select|Wrap|Line Numbers Scrap%: IIf(Nz([GoodParts]) <> 0, Nz([BadParts])/[GoodParts], ???)   Where I have got ??? you can either substitute 0 or omit the final comma & ??? to show a blank Phil Jul 18 '18 #3

 100+ P: 120 Thanks Rabbit and Phil, that seemed to fix it(So far). Phil, We did, but once I started to build a report off of the query I was getting this error. Never thought having it backwards like that would make a difference, as it was working on the form... Again thanks for the gentle smack upside the head. Jul 18 '18 #4

 Expert Mod 2.5K+ P: 3,158 It is also often helpful to make sure your numbers are never Null, if you intend to do any calculations on them. If you are performing a `Count()` on either your GoodParts or BadParts, those values should never be Null, as a `Count()` will always return 0 if there are no records matching your criteria. This can be helpful. Then you only have to worry about catching the Divide By Zero in your final calculations. Just something to think about as you move forward. Jul 18 '18 #5

 Expert Mod 15k+ P: 31,302 I can certainly say I've fallen over that one in the past. Well caught Rabbit & Phil. I've selected Rabbit's as BA as it was on the money and the first reply. Jul 19 '18 #6