Calculation is giving me an error

[bhrosey via AccessMonster.com]

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

[Keith Wilby]

"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

[bhrosey via AccessMonster.com]

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

[bhrosey via AccessMonster.com]

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

[bhrosey via AccessMonster.com]

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

[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

[msg2hany]

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