Solved

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

Posted on 2011-03-24
7
657 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 - Access MVP) 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

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)

Question has a verified solution.

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

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now