469,352 Members | 2,140 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,352 developers. It's quick & easy.

Divide by 0 or null

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
5 11606
12,516 Expert Mod 8TB
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
I want to treat it as zero
Feb 16 '07 #3
12,516 Expert Mod 8TB
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
3,080 Expert 2GB
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
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.

Similar topics

4 posts views Thread by Jack Smith | last post: by
3 posts views Thread by Ryan | last post: by
6 posts views Thread by Nobody | last post: by
2 posts views Thread by Mike Leahy | last post: by
5 posts views Thread by per.nordlow | last post: by
4 posts views Thread by shuisheng | last post: by
1 post views Thread by Justin.Velazquez | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.