Using Select Case with Strings

[shadow9449]

An alternative construct.

DIM IntCase as Integer
IntCase = 0
if like "apples*" then IntCase =1
if like "oranges*" then IntCase =2
if like "something weird" then IntCase =999
Select Case IntCase
Case 0 
       No match Error message
Case 1
       Code for this situation
Case 2
       Code of this situation
Case Else
        A second opportunity for an error message
End Select

Yes, that's the kind of thing I would have done had I used the If approach. The SWITCH approach in a query worked really well. As I said earlier, I tested it with hundreds of thousands of records of live data and it returned the query results instantly.

Of course Access 2007 might give a different result but Access 2007 users are used to everything being slow

SHADOW

 

[Banana]

That looks like the syntax I was looking for. I don't understand why the compiler accepts your syntax but not the syntax I was using but languages can be really weird...

SHADOW

Simply because ChrisO's syntax presented a boolean expression, which is something the Select Case can compare to. I don't know what help file said but I suppose it was "kind of" telling the truth when it said it couldn't do a Like like this:

Select Case foo
   Case Like bar

but apparently the author of the help file didn't bother to tell you that by rearranging the expression from a comparison expression to a boolean expression, you could do the desired operation just as well.




ChrisO--

Shame on me for forgetting that useful trick, especially after I told you that it was very useful trick. Tsk tsk.

 

[ChrisO]

Well, I guess it’s really a matter of what we prefer to do or read.

Here’s the equivalent If structure: -

Function GetType(ByVal strIn As String) As String

    If strIn Like "apples*" Then
        GetType = "apples"
    ElseIf strIn Like "oranges*" Then
        GetType = "oranges"
    Else
        GetType = "not fruit in list"
    End If

End Function

You can see the similarity.

 

[Steve R.]

It’s not really an alternative if you are not using ‘Like’ within the Select Case.

The "LIKE" statement is not within the Select Case statement it precedes it.