By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,336 Members | 2,307 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,336 IT Pros & Developers. It's quick & easy.

Divide by 0 or null

P: 4
I am hoping this is an easy question for someone out there.

I am running a query where I am dividing (fieldA/fieldB), there are instances where Field A and Field B have a zero value.
For example if in January Field A has a value of 4 and Field B has a value of 2. I get 2 as my results but
for February if Field A is zero and Field B is zero, i get and #error message therefore I can't calculate an average for the year.

Please help, I am new to MSaccess and this is causing me some grief.

thank you in advance for you help.

Feb 16 '07 #1
Share this Question
Share on Google+
5 Replies

Expert Mod 10K+
P: 12,366
It depends on how you want to deal with the double zero situation. Do you want to ignore it from the calculation of the average? Or do you want to treat it as a 0 which would bring down your average?
Feb 16 '07 #2

P: 4
I want to treat it as zero
Feb 16 '07 #3

Expert Mod 10K+
P: 12,366
You'll want to check for a 0 in the denominator.
Expand|Select|Wrap|Line Numbers
  1. iif(value2 = 0, 0, value1/value2)
Feb 16 '07 #4

Expert 2.5K+
P: 3,072
To be entirely safe add the NZ() function for all fields you use in calculations like:

iif(NZ(value2) = 0, 0, NZ(value1)/NZ(value2))

Otherwise Null values can give trouble...

Feb 16 '07 #5

P: 4
Thank you very much.

Your instructions worked.
Feb 16 '07 #6

Post your reply

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