Problems dividing by a null value to find a percentage

[Lpitt56]

I have a query the rolls up a qty recieved per month and a qty rejected per month and then calculates a right the first time percentage by simply 1-(qty rejected/qty received). Work fine unless I have a month where there are no rejects. Instead of return 100% the field is just blank. I can use the same method in an Excel sheet and it calculates the percentage as 100% even with the dividor blank, but in my Access Query it just returns a blank for that month. I have an attachment to this post that shows the query. What am I missing?

[TheSmileyCoder]

Hi Lpitt56

For more background information on null look at:
What is null?

Basicly, the problem comes from the fact that null is NOT the same as 0. Simple example. Imagine this simple example:
I hand you a black bag, and tell you there are apples inside. I dont tell you how many and you are not allowed to look. You have NULL information on the amount of apples. If I ask you to add / subtract / divide, you would still have NULL information on the result. That is similar to what is going on here.

However, sometimes we as programmers know that the absence of a value IS equal to 0. We can then convert the null to something harmless, for instance a 0. This can be done by the Nz function (READ THE Access help on it)

Nz(Variable, ALTERNATE_Variable)

If variable is NULL, it will return the ALTERNATE_Variable.

Example usage in your case:

1-(nz(qty rejected,0)/qty received)

[Lpitt56]

Thanks for the info! Your reccomendation worked great!

[TheSmileyCoder]

Your welcome. Glad to hear it worked for you.