Solved

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

Posted on 2011-03-24
7
658 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
  • 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

809 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