By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,882 Members | 2,082 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
9 Replies


maxamis4
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

missinglinq
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

NeoPa
Expert Mod 15k+
P: 31,470
Expand|Select|Wrap|Line Numbers
  1. 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
ozz
Expand|Select|Wrap|Line Numbers
  1. 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
ozz
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

NeoPa
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
  1. =[forOrders]![StateOrProvince]
Then enter the other items if that doesn't return #NAME?
May 7 '07 #7

P: 4
ozz
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
  1. =[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

NeoPa
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

ADezii
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
  1. Val(IIf(intTest = 25, 1, 0))
May 9 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.