Solved

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

Posted on 2011-03-24
7
659 Views
Last Modified: 2012-05-11
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
Comment
Question by:AkAlan
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 250 total points
ID: 35211481
Unfortunately, the IN() operator does not work in VBA, only SQL.

mx

0
 
LVL 75
ID: 35211490
You might look at the Switch() and Choose() functions ... which do work in VBA code.

mx
0
 
LVL 6

Author Comment

by:AkAlan
ID: 35211518
OK, I thought so, just thought it was worth a shot. Thanks.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 75
ID: 35211549
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
 
LVL 6

Author Comment

by:AkAlan
ID: 35211568
I went with the case statement. Thanks.
0
 
LVL 75
ID: 35211581
Be sure to check out Switch() and Choose() ... two less-well-know functions that are VERY handy.

mx
0
 
LVL 57
ID: 35213446
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

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question