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

Calculation is giving me an error

P: n/a
I have a calculated field on a form that has this formula:

=IIf([Field1]<>0,[Field2]/([Field3]-[Field1]),[Field2]/[Field3])

The problem is when the if statement is false, it's partly because Field2 is
zero, so when it tries to devide 0/Field3 it gives me an error (#Num!). I
would like to get the result "null" and leave the field blank or return "N/A".
ALSO, I have 5 groups that I use this same formula on and there is another
calculated field that gives me an average of all 5 groups that looks like
this:

=([group1]+[group2]+[group3]+[group4]+[group5])/5

Of course, when I get the #Num! error above it returns a #Error in this
calculated field.

How can I fix this problem? Thanks in advance, you guys are all awesome.
You've helped me so much just by reading other peoples' questions. Thanks a
million!!

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1

May 24 '07 #1
Share this Question
Share on Google+
6 Replies

P: n/a
"bhrosey via AccessMonster.com" <u33878@uwewrote in message
news:72a7eee3d4fc1@uwe...
>I have a calculated field on a form that has this formula:

=IIf([Field1]<>0,[Field2]/([Field3]-[Field1]),[Field2]/[Field3])

The problem is when the if statement is false, it's partly because Field2
is
zero, so when it tries to devide 0/Field3 it gives me an error (#Num!). I
would like to get the result "null" and leave the field blank or return
"N/A".
It looks like you need an extra clause in your IIf (untested):

=IIf([Field1]<>0,[Field2]/([Field3]-[Field1]),IIf([Field2]=0,Null,[Field2]/[Field3]))

Keith.
www.keithwilby.com

May 24 '07 #2

P: n/a
Keith, Thank you! That was close, but you pointed me in the right direction
and I was able to fix that part of it by using this formula:

=IIf([Field1]=[field3],IIf([Field2]=0,null,[Field2]/([Field3]-[Field1])),
[Field2]/[Field3])
because the way you suggested, there could still be a valid value in [field4]
](which doesn't actually figure into the calculation, itself) and by adding
JUST your second IIf statement, it still ignored [field4). The only way I
want to return null is if [field2] AND [field4] are both ZERO and I can tell
that by [field1] and [field3] being equal without having to figure [field4]
into the equation. NOW, that works, but how do I get my other formula to
ignore the null value that I just created?

Keith Wilby wrote:
>>I have a calculated field on a form that has this formula:
[quoted text clipped - 5 lines]
>would like to get the result "null" and leave the field blank or return
"N/A".

It looks like you need an extra clause in your IIf (untested):

=IIf([Field1]<>0,[Field2]/([Field3]-[Field1]),IIf([Field2]=0,Null,[Field2]/[Field3]))

Keith.
www.keithwilby.com
--
Message posted via http://www.accessmonster.com

May 24 '07 #3

P: n/a
Sorry, I guess I should have included [field4] in my original question. I
didn't think it would figure into things, but I guess it did.

bhrosey wrote:
>Keith, Thank you! That was close, but you pointed me in the right direction
and I was able to fix that part of it by using this formula:

=IIf([Field1]=[field3],IIf([Field2]=0,null,[Field2]/([Field3]-[Field1])),
[Field2]/[Field3])
because the way you suggested, there could still be a valid value in [field4]
](which doesn't actually figure into the calculation, itself) and by adding
JUST your second IIf statement, it still ignored [field4). The only way I
want to return null is if [field2] AND [field4] are both ZERO and I can tell
that by [field1] and [field3] being equal without having to figure [field4]
into the equation. NOW, that works, but how do I get my other formula to
ignore the null value that I just created?
>>>I have a calculated field on a form that has this formula:
[quoted text clipped - 8 lines]
>>Keith.
www.keithwilby.com
--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200705/1

May 24 '07 #4

P: n/a
bhrosey wrote:
>Sorry, I guess I should have included [field4] in my original question. I
didn't think it would figure into things, but I guess it did.
>>Keith, Thank you! That was close, but you pointed me in the right direction
and I was able to fix that part of it by using this formula:
[quoted text clipped - 14 lines]
>>>Keith.
www.keithwilby.com
Actually, this didn't work either, but I did manage to get it fixed with a
much simpler formula.

=IIf([field3]=[field1],Null,[field2]/([field1]-[field3]))

--
Message posted via http://www.accessmonster.com

May 24 '07 #5

P: n/a
bhrosey via AccessMonster.com wrote:
I have a calculated field on a form that has this formula:

=IIf([Field1]<>0,[Field2]/([Field3]-[Field1]),[Field2]/[Field3])

The problem is when the if statement is false, it's partly because Field2 is
zero, so when it tries to devide 0/Field3 it gives me an error (#Num!).
Then your field3 is 0. You need to deal with that.
I
would like to get the result "null" and leave the field blank or return "N/A".
iif(field1 = 0, "N/A", <do stuff>)


--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
May 25 '07 #6

P: n/a
I think there is an ERROR in the commas and practices...

Hany
www.access-forum.com

Tim Marshall :
bhrosey via AccessMonster.com wrote:
I have a calculated field on a form that has this formula:

=IIf([Field1]<>0,[Field2]/([Field3]-[Field1]),[Field2]/[Field3])

The problem is when the if statement is false, it's partly because Field2 is
zero, so when it tries to devide 0/Field3 it gives me an error (#Num!).

Then your field3 is 0. You need to deal with that.
I
would like to get the result "null" and leave the field blank or return "N/A".

iif(field1 = 0, "N/A", <do stuff>)


--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Be Careful, Big Bird!" - Ditto "TIM-MAY!!" - Me
May 26 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.