Switch function help

[nithink]

Hi,
Help needed with Switch function. Very much new to
MS_Access.
I've a column with datatype TEXT. It has got both numbers
and text in it.I want to select the values from that column
according to a condition.

If the column values are < 100 I want to append with 22- .
So it has to be like 22-99
22-98
22-97 and so on..

If the column values are > 100 I want to append with 33- .
So it has to be like 33-100
33-101
33-102 and so on..

If the column values are alpha or alphanumeric, I want to append with 22- . So it has to be like 22-YELLOW
22-RED13
22-GREEN14 and so on..


For this I used Switch function. But I dont know how to decode the alpha or alphanumeric values.

SELECT switch([table1.acctnum]<"100","22-"&table1.acctnum,
[table1.acctnum]>"100","33-"&table1.acctnum)
AS acctnum
FROM table1

Thanks much.
GR

 

[TonyJollans]

Hi new2everything,

I wouldn't use Switch for this; I would just use a couple of IIFs. Whichever, you need to use the IsNumeric Function ..

[blue][tt]SELECT ...,
IIf(IsNumeric([table1].[acctnum]),IIf(Val([table1].[acctnum])>=100,"33-","22-"),"22-") & [table1].[acctnum]
FROM ...[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[nithink]

Hi Tony,
I tried using Iif as you said in the query but its just giving the ouput as 22- and nothing else.. Can you pls help me out in this ? Can't figure out whats the problem..

Thanks,
-GR

 

[nithink]

It did work. Thanks so much. Somewhere I missed the brackets. Pls ignore the previous post I made.

I removed switch and used IIfs.

Thanks
GR