MS Access VBA

In my following code does VBA have an "IN" statement like SQL?

I'm trying to avoid a bunch of "OR" statements
(Or Me.[Submit Stage] = "Reject Again"   etc...)

If Me.[Submit Stage] = "Reject Submit" Then
    Me.chkSuggestJunk.Enabled = False
Else
    Me.chkSuggestJunk.Enabled = True
End If

Open in new window

Larry Bristersr. DeveloperAsked:
Who is Participating?
 
CluskittCommented:
Use Select:
Select Case Me.[Submit Stage]
  Case "Reject Submit","Some Other Check","Yet Another Check"
    Me.chkSuggestJunk.Enabled = False
  Case Else
    Me.chkSuggestJunk.Enabled = True
End Select
0
 
jjnet123Commented:
You could also use InStr if you're looking for a particular word in the string, for example:

If Instr(Me.[Submit Stage],"Reject") Then
    Me.chkSuggestJunk.Enabled = False
Else
    Me.chkSuggestJunk.Enabled = True
End If
0
 
Larry Bristersr. DeveloperAuthor Commented:
Well...
Duh on me!

I knew that.  For some reason being in VB.Net for so long I forget that VBA isn't completly alien.
0
 
Dale FyeCommented:
I have a function I use IsIn(), which accepts a value and potential matches and returns a boolean value.

If IsIn(me.[Submit Stage], "Reject Submit","Some Other Check","Yet Another Check") Then
   'do something here
Else
   'do something else
endif

Public Function IsIn(TestValue As Variant, ParamArray ArrayOfValues() As Variant) As Boolean

    'Tests to see whether a value is in a list of other values.
    'Similar to both the SQL IN ( ) clause and SELECT Case statements
    'Accepts a value of Variant data type, followed by an array of values.
    'Checks to see if the first value is in the array.
    Dim intLoop As Integer
    
    IsIn = False
    
    If IsNull(TestValue) Then Exit Function
    
    For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
        If TestValue = ArrayOfValues(intLoop) Then
            IsIn = True
            Exit For
        End If
    Next
    
End Function

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.