[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 624
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
2 Solutions
 
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
 
lrbristerAuthor 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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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