• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 675
  • Last Modified:

Is there a VBA operator or function like the T-Sql In() operator?

I want to test a variable value against several values like is done in a sql Where clause.

Like this:

If myVar In('a','b','c') Then
End IF

I know I can use a CASE or nested If Then Else statements but would like to know if there are other options.
0
AkAlan
Asked:
AkAlan
  • 4
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Unfortunately, the IN() operator does not work in VBA, only SQL.

mx

0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You might look at the Switch() and Choose() functions ... which do work in VBA code.

mx
0
 
AkAlanAuthor Commented:
OK, I thought so, just thought it was worth a shot. Thanks.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Kind of odd that it is not ...
I should you if you have a SQL (String) statement you are creating in VBA code ... IN() can be used in that context.

I *might* be possible wrapping IN() inside the Eval() function ... I tried a couple of quick things, but could not get it to work ...

mx
0
 
AkAlanAuthor Commented:
I went with the case statement. Thanks.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Be sure to check out Switch() and Choose() ... two less-well-know functions that are VERY handy.

mx
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
FYI, no reason you can't write your own like this:

Function SearchIN(strSearch, ParamArray varInArray() As Variant) As Boolean

  Dim varIN As Variant

  SearchIN = False

  For Each varIN In varInArray()
    If strSearch = varIN Then
       SearchIN = True
       Exit For
    End If
  Next

End Function

If SearchIN(strCity,"Washington","Buffalo","LA") Then

JimD.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now