Using "Like" Operator in "Select Case" Statement

[LA Lawyer]

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?

 

[Marshall Barton]

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?

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"
...

Incidentally, is the Like operator case sensitive?

No

 

[Tom van Stiphout]

On Wed, 3 Feb 2010 13:44:21 -0800, "LA Lawyer" <[email protected]>
wrote:

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

You can do this, and it's 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're hinted at would require a technology like
LINQ which is not part of VBA.

-Tom.
Microsoft Access MVP