How to use Switch/Case Statement in Access Query 2

[vpellerito]

I'm currently using the IIf function in my access queries when I need to test against values, but I was wondering if there was a switch or case function that I could use instead. I've used "Select Case" in VB but I know that won't work in an Access query.
Is there such a function that works in Access queries?
Thanks.
Vince

 

[traingamer]

There is not. But you could write your own function and reference it in the query.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[vpellerito]

Oh that's right. I forgot that was possible.
Thanks!
Vince

 

[MajP]

Of course there is. It is a vba function. VBA functions work in Access queries.

SELECT tblOne.textField, Switch([textField]="A",1,[textField]="B",2,[textField]="C",3) AS NewNum
FROM tblOne;

textField  NewNum
a          1
b          2
c          3

But this is so painful to write and debug. Roll your own vb function and use it instead

However, nine times out of 10 this can be done far simpler, far more flexible, with a reference table

tblConvert
ltr text
num long

a 1
B 2
c 3

 

[traingamer]

I'd completely forgotten about Switch().

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MajP]

However, although this can be done I would never use the switch in a query and unlikely to do this in a user defined function. For most situations using a reference table is
1)More efficient
2)More reuseable
3)Easier to build
4)More flexible
5)Less likely to error
...

Imagine a table with 100,000 records. Doing this with a reference table would be almost instantaneous. A switch or UDF would bog down.