Using an IN statement in VBA (1 Viewer)

[supmktg]

I'm trying to replace a cumbersome multiple OR statement with an IN statement, but it is erroring out (in A2K).

Instead of this code:

If str = "A" OR str = "B" OR str = "C" OR str = "AB" OR str = "BC" Then
'do stuff
End If

I'd like to use this code:

IF str IN ("A", "B", "C", "AB, "AC") Then
'do stuff'
End If

The IN statement gives me a Compile Error 'Expected: Then or GoTo'. Can someone tell me what is wrong with my IN statement?

Thanks,
Sup

 

[jdraw]

My guess is that you are thinking of the IN clause in SQL

SELECT tbl.* from Tbl
where MyField IN ("a","b","c","d")

 

[MarkK]

Yeah, 'IN' is SQL, not VBA. But VBA has a Select Case ...

  Select Case str
    Case "A", "B", "C", "AB", "AC"
      [COLOR="Green"]'do stuff[/COLOR]
    Case Else
      [COLOR="Green"]'do other stuff[/COLOR]
  End Select

 

[vbaInet]

Just by the way. If you had this:

Function GetStr() As String
    GetStr = "A"
End Function

You could have done this:

IF Eval("GetStr() IN ('A', 'B', 'C', 'AB, 'AC')") Then
    'do stuff'
End If

Notice I changed str to something else because Str() is a function in VBA and should not be used as a variable name.

But, I would do it lagbolt's way.

 

[MSAccessRookie]

I'm trying to replace a cumbersome multiple OR statement with an IN statement, but it is erroring out (in A2K).

Instead of this code:

If str = "A" OR str = "B" OR str = "C" OR str = "AB" OR str = "BC" Then
'do stuff
End If

I'd like to use this code:

IF str IN ("A", "B", "C", "AB[SIZE=4][COLOR=red][B]"[/B][/COLOR][/SIZE], "AC") Then
'do stuff'
End If

The IN statement gives me a Compile Error 'Expected: Then or GoTo'. Can someone tell me what is wrong with my IN statement?

Thanks,
Sup

The SQL Code that uses IN(), is missing a " character (Refer to the code above). Either the missing " is a typo, or it could be the source of your problem. Since no one else has mentioned anything about the " so far, I thought I would.

 

[vbaInet]

Good catch MSAccessRookie and definitely worth mentioning.

However, the source of the problem is the IN () statement can't be used directly in the VBA editor (as lagbolt mentioned) unless via Eval().

 

[supmktg]

Using a case statement never occurred to me. That will work perfectly.

Thanks,
Sup

 

[MarkK]

How come that works in Eval()? I don't get that.

Also, you can use SQL in VBA if you open a cheesey recordset like ...

Sub RunSQLOperationWithoutUsingATable()
   Dim tmp As String
   tmp = "A"
   MsgBox CurrentDb.OpenRecordset( _
      "SELECT '" & tmp & "' IN ('A', 'B', 'C', 'AB', 'AC')" _
      ).Fields(0)
End Sub

 

[vbaInet]

How come that works in Eval()? I don't get that.

My suspicion is that Eval() is the "ultimate" parser. Eval() can interpret both SQL specific functions and VBA specific functions. Take for example, calling the second column of a combo box in a query:

[Forms]![FormName]![ComboBoxName].[Column][COLOR=Red](1)[/COLOR]

The red part bombs because the engine is expecting a function, but when you Eval() that statement, everything's fine and dandy:

Eval("[Forms]![FormName]![ComboBoxName].[Column](1)")

 

[smig]

this is a nice tip vbaInet