"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:
Code:
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 |