DataBase - Using "Like" Operator in "Select Case" Statement

Asked By LA Lawyer

03-Feb-10 04:44 PM

I want to use a  "like" operator in a Select Case statement; I am sure that
there is some way to do something like this.  Here is what I have to do:

Select Case ClassOfContact
Case  "Bob's client", "Mary's client", "Our clients". . . . .
......
Case "Vendors"
......
End Select

Obviously, it would be safer and faster to use something like a Like
operator, such as "Like "*client*", but Access 2007 rejects the Like
operator.

Is there a way to do that?  Is this a limitation of Select Case?  What are
my options?  What do the smart people do?

Incidentally, is the Like operator case sensitive?

03-Feb-10 06:50 PM

Yes, it is a limitation of Select Case

An alternative could be to use something like:

Select Case True
Case ClassOfContact Like "*client*"
...
Case ClassOfContact = "Vendors"
...

No

--
Marsh
MVP [MS Access]

03-Feb-10 07:04 PM

You write "I am sure...". What makes you so sure?

You can do this, and it is close to what you want but no cigar:
Dim s As String
s = "aaa client"
Select Case s Like "*client*"
Case True
Debug.Print "True"
Case Else
Debug.Print "Else"
End Select
If you want to know how this works, look up "Like Operator" in the
help file.

The more general case you are hinted at would require a technology like
LINQ which is not part of VBA.

-Tom.
Microsoft Access MVP