Using Select Case with Strings (1 Viewer)

shadow9449 · Jan 1, 2010

An alternative construct.

Code:

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 · Jan 1, 2010

shadow9449 said:
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:

Code:

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 · Jan 1, 2010

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

Here’s the equivalent If structure: -

Code:

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. · Jan 1, 2010

ChrisO said:
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.

Using Select Case with Strings (1 Viewer)

shadow9449

Registered User.

Banana

split with a cherry atop.

ChrisO

Registered User.

Steve R.

Retired

Similar threads

Users who are viewing this thread