iif statement using a range (between)

[denver1717]

ACCOUNT
400001
400002
400003
400004
400005
400006
400008
400012
400013
400075
400076
400077
420001
420002
420003
420004
420005
420006
420008
420010
420012
420013
420075
420076
420077

Question 1: What is the function that converts text to a number? I am working with the above, which I want to leave in the original table as text, but I would like to set up a query that contains both the text version, and a formula that converts the text to a number format so I can build an expression (I am thinking I probably can't use greater than/less than on a text format number).

Question 2: I want to build an column called TYPE using an if statement that states if the ACCOUNT is > 400000 or < 400099, assign a value of "IP", and if the ACCOUNT is <420000 or <420099. I would also like to include in this expression if the ACCOUNT does not fall in these ranges, return the text value "Error".

I built the following, but Access hates it:
TYPE: IIf([ACCOUNT]>=400000 And [ACCOUNT]<=400099,"IP",IIf([ACCOUNT]>=420000 And [Lawson]![ACCOUNT]<=420099,"OP","Error"))



Thanks for any help!

[alansidman]

Look here at the Val function

http://www.techonthenet.com/access/f...umeric/val.php

[ssanfu]

"Type" is a reserved word in Access and shouldn't be used for object names (plus it is not very descriptive - "Type" of what??). A better name might be "AcctType".

What is "[Lawson]![ACCOUNT]?

I created a table and a query. For the AcctType, you cannot use the "Account" (text values) in the IIF() statement - use the column that is converted to a number.
SQL for query:

SELECT Account.ACCOUNT, Val([ACCOUNT]) AS AcctVal, IIf([AcctVal]>=400000 And [AcctVal]<=400099,"IP",IIf([AcctVal]>=420000 And [AcctVal]<=420099,"OP","Error")) AS AcctType
FROM Account;

Query result:

ACCOUNT	AcctVal	AcctType
400001	400001	IP
400002	400002	IP
400003	400003	IP
400004	400004	IP
400005	400005	IP
400006	400006	IP
400008	400008	IP
400012	400012	IP
400013	400013	IP
400075	400075	IP
400076	400076	IP
400077	400077	IP
420001	420001	OP
420002	420002	OP
420003	420003	OP
420004	420004	OP
420005	420005	OP
420006	420006	OP
420008	420008	OP
420010	420010	OP
420012	420012	OP
420013	420013	OP
420075	420075	OP
420076	420076	OP
420077	420077	OP

[denver1717]

Awesome, thank you for taking the time to help me out!! Did not know that about "Type" in Access, either - will be sure to use naming conventions similar to the examples you used.

(ps - Lawson was the name of the table I was working with)

"Type" is a reserved word in Access and shouldn't be used for object names (plus it is not very descriptive - "Type" of what??). A better name might be "AcctType".

What is "[Lawson]![ACCOUNT]?

I created a table and a query. For the AcctType, you cannot use the "Account" (text values) in the IIF() statement - use the column that is converted to a number.
SQL for query:

SELECT Account.ACCOUNT, Val([ACCOUNT]) AS AcctVal, IIf([AcctVal]>=400000 And [AcctVal]<=400099,"IP",IIf([AcctVal]>=420000 And [AcctVal]<=420099,"OP","Error")) AS AcctType
FROM Account;

Query result: