423,473 Members | 2,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,473 IT Pros & Developers. It's quick & easy.

Query "Division by Zero" problem

P: 96
Expand|Select|Wrap|Line Numbers
  1. 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

✓ answered by Rabbit

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.

Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,264
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

PhilOfWalton
Expert 100+
P: 1,353
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
  1. Scrap%: IIf(Nz([GoodParts]) <> 0, Nz([BadParts])/[GoodParts], ???)
  2.  
Where I have got ??? you can either substitute 0 or omit the final comma & ??? to show a blank

Phil
Jul 18 '18 #3

P: 96
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

twinnyfo
Expert Mod 2.5K+
P: 2,605
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

NeoPa
Expert Mod 15k+
P: 31,031
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

Post your reply

Sign in to post your reply or Sign up for a free account.