Solved

VBA - Multiple OR In a statement.

Posted on 2012-03-28
11
622 Views
Last Modified: 2012-03-31
This may seem like a very basic programming question in VBA but for some reason I can not get this statement to work.  I'm fairly new to VBA.

Here is the statement.

If producttype <> "ABC" Or producttype <> "DEF" Or producttype <> "GHI" Or producttype <> "JKL" Then
     MsgBox ExportRequest, vbInformation, "No Excel Form for Product Type"
    Exit Sub

If I limit it to one <> then it works for that one product type.  

How should this be written in VBA?
0
Comment
Question by:CompTech810
  • 3
  • 2
  • 2
  • +2
11 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 334 total points
ID: 37776819
I think you need ANDS:

If producttype <> "ABC" AND producttype <> "DEF" AND producttype <> "GHI" AND producttype <> "JKL" Then
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 334 total points
ID: 37776826
Alternatively Using OR:

If  NOT (producttype = "ABC" Or producttype = "DEF" OR producttype = "GHI" Or producttype = "JKL") Then
0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 166 total points
ID: 37776835
You need to use ANDs instead

If producttype <> "ABC" And producttype <> "DEF" And producttype <> "GHI" And producttype <> "JKL" Then
     MsgBox ExportRequest, vbInformation, "No Excel Form for Product Type"
    Exit Sub

Open in new window

0
 
LVL 2

Author Comment

by:CompTech810
ID: 37776840
I feel like such an airhead!!  That worked.  Between all the different programming languages I've used I have again confused myself.  Thank You!
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 33

Expert Comment

by:Norie
ID: 37776845
You could use a Select Case statement.

This will only show the message box if producttype is not equal to ABC, DEF etc

If producttype is ABC or DEF or GHI then it will do nothing but that can be changed if required.
Select Case producttype

      Case  "ABC",  "DEF", "GHI","JKL"
              ' do nothing
      Case Else
                MsgBox ExportRequest, vbInformation, "No Excel Form for Product Type"
                Exit Sub
End Select

Open in new window

0
 
LVL 61

Expert Comment

by:mbizup
ID: 37776846
Glad to help out :)

... jacko's solution is exactly what I posted in the first comment
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37776865
I use a function I wrote a several years ago for these types of checks.  It allows me to test to see whether a value is listed among an array of other values passed to the function.  The function looks like:

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 argument is in the array
    'Ignors NULL values passed in the array

    Dim intLoop As Integer
   
    IsIn = False
   
    If IsNull(TestValue) Then Exit Function
   
    For intLoop = LBound(ArrayOfValues) To UBound(ArrayOfValues)
        isnull(ArrayOfValues(intLoop)) Then
            'ignore that value
        ElseIf TestValue = ArrayOfValues(intLoop) Then
            IsIn = True
            Exit For
        End If
    Next
   
End Function


To use this function in your case, you would simply use the syntax:

If IsIn(ProductType, "ABC", "DEF", "GHI", "JKL") = False Then
    'do something here
Else
    'do somthing else
endif
0
 
LVL 33

Expert Comment

by:Norie
ID: 37776907
fyed

Does it work if you pass an actual array?

For example like this.
    arr = Array(1, 2, 3, 4, 5)

    If IsIn(1, arr)  Then
   ....

Open in new window

0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 37777042
No, I so rarely use arrays that I didn't think of that initially.

However, over time, I have made several slightly modified versions to handle

1.  arrays
2.  comma separated strings (like you might get from an inputbox)

but don't have either of those versions handy.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
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…

920 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

13 Experts available now in Live!

Get 1:1 Help Now