MS Access VBA

Posted on 2012-08-22
Last Modified: 2012-08-22
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
    Me.chkSuggestJunk.Enabled = True
End If

Open in new window

Question by:lrbrister
    LVL 18

    Accepted Solution

    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
    LVL 1

    Assisted Solution

    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
        Me.chkSuggestJunk.Enabled = True
    End If

    Author Closing Comment

    Duh on me!

    I knew that.  For some reason being in VB.Net for so long I forget that VBA isn't completly alien.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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
       'do something else

    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
    End Function

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
    The canonical version of this article is on my web site here: A companion presentation is available here:
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    779 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

    14 Experts available now in Live!

    Get 1:1 Help Now