• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 669
  • 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 MVP, Access and Data Platform)Commented:
Unfortunately, the IN() operator does not work in VBA, only SQL.

mx

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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)PresidentCommented:
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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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