464,332 Members | 1,148 Online
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
6 Replies

 P: n/a "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". 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 directionand 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 addingJUST your second IIf statement, it still ignored [field4). The only way Iwant to return null is if [field2] AND [field4] are both ZERO and I can tellthat 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 toignore 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. Ididn'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 directionand 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", ) -- 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", ) -- 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