431,882 Members | 2,082 Online
Need help? Post your question and get tips & solutions from a community of 431,882 IT Pros & Developers. It's quick & easy.

# text to number conversion

 P: 4 I want to convert the result of the IIf function to a number. Like IIF(test=25,1,0) It looks like the above function evaluates to text '1' or '0' not to numbers 1 and 0 that I can use in a mathematical expression. Is there a way to change the result to a number? I tried CODE(text), but it did not help. Thanks May 4 '07 #1
9 Replies

 Expert 100+ P: 295 I would set your value i as integer and then do the following i = Val(test) this will return a true number for you This explains how it works http://www.techonthenet.com/access/functions/numeric/val.php I want to convert the result of the IIf function to a number. Like IIF(test=25,1,0) It looks like the above function evaluates to text '1' or '0' not to numbers 1 and 0 that I can use in a mathematical expression. Is there a way to change the result to a number? I tried CODE(text), but it did not help. Thanks May 4 '07 #2

 Expert 2.5K+ P: 3,532 Why do you say "It looks like the above function evaluates to text '1' or '0' not to numbers 1 and 0 that I can use in a mathematical expression?" It should only evaluate to text "1" or text "0" if you set it up that way. For instance, X = IIF(test = 25, 1, 0) * 5 X will be 5 if test = 25 and X = 0 if test <> 25 How exactly are you trying to use it? May 5 '07 #3

 Expert Mod 15k+ P: 31,470 Expand|Select|Wrap|Line Numbers IIf(test=25,1,0) will always return numeric values. It is possible that something else in your code is causing an automatic conversion to text. What's the context? May 5 '07 #4

 P: 4 Expand|Select|Wrap|Line Numbers IIf(test=25,1,0) will always return numeric values. It is possible that something else in your code is causing an automatic conversion to text. What's the context? I have the expression below in expression builder so that a sales tax is added only when the state is Arizona and 0 for other states, but the value entered is #NAME? I have also tried CODE(IIf([forOrders]![StateOrProvince]=[AZ],1,0)) with the same result. =[forOrders]![SalesTxRate]*[Order Subtotal]*(IIf([forOrders]![StateOrProvince]=[AZ],1,0)) May 7 '07 #5

 P: 4 I would set your value i as integer and then do the following i = Val(test) this will return a true number for you This explains how it works http://www.techonthenet.com/access/functions/numeric/val.php I tried it but result is same. #NAME? =[forOrders]![SalesTxRate]*[Order Subtotal]*Val((IIf([forOrders]![StateOrProvince]=[AZ],1,0))) Any suggestions? May 7 '07 #6

 Expert Mod 15k+ P: 31,470 Take bits out of the equation until you have the item which causes the #NAME? EG. Just enter (for testing purposes : Expand|Select|Wrap|Line Numbers =[forOrders]![StateOrProvince] Then enter the other items if that doesn't return #NAME? May 7 '07 #7

 P: 4 Take bits out of the equation until you have the item which causes the #NAME? EG. Just enter (for testing purposes : Expand|Select|Wrap|Line Numbers =[forOrders]![StateOrProvince] Then enter the other items if that doesn't return #NAME? Thank you, Neopa and All. problem was the IIf function IIf(state=[AZ],1,0) required the state to be typed as "AZ". May 8 '07 #8

 Expert Mod 15k+ P: 31,470 You're welcome Ozz. That's a debugging technique that'll certainly come in handy many many times in your life. May 9 '07 #9

 Expert 5K+ P: 8,628 I want to convert the result of the IIf function to a number. Like IIF(test=25,1,0) It looks like the above function evaluates to text '1' or '0' not to numbers 1 and 0 that I can use in a mathematical expression. Is there a way to change the result to a number? I tried CODE(text), but it did not help. Thanks Expand|Select|Wrap|Line Numbers Val(IIf(intTest = 25, 1, 0)) May 9 '07 #10