In Access 2007 VBA, how would I use ranges/choices in conditions such as If...Then?

If intVar could possible be an integer 1,2,3...10, how could I check for that in an If...Then structure. IOW, is it possible to do something like this:

    If intVar = [1,2,3] Then blah


    If intVar = [3-5] Then blah

With strings?

    If strVar = ["X","Y","Z"] Then blah


    If strVar = ["N"-"Z"] Then blah

I know Select...Case is another way. I want to know if this is possible. This proved difficult to research in Access help and Google.

LVL 13
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

for numerics range you can do

   If intVar = [1,2,3] Then blah
  if intvar =1 or intVar = 2 or  intVar=3 Then blah

    If intVar = [3-5] Then blah
  if intvar >=3 and intVar<=5 Then blah

for string you can do

 If strVar = ["X","Y","Z"] Then blah
if strVar = "X" or strVar="Y" or strVar="Z" Then blah

 If strVar = ["N"-"Z"] Then blah
If Asc(strVar) >= Asc("N") And Asc(strVar) <= Asc("Z") Then blah
is you dont have overlapping conditions it is preferable to use the select statement
Select Case intVar
Case 1 to 3

Case 3 to 5

Case Else

End Select

you can do the same with strings...below is the VBA help on this..and attached an example

Executes one of several groups of statements, depending on the value of an expression.


Select Case testexpression
[Case expressionlist-n
[statements-n]] ...
[Case Else

End Select

The Select Case statement syntax has these parts:

Part Description
testexpression Required. Any numeric expression or string expression.
expressionlist-n Required if a Case appears. Delimited list of one or more of the following forms: expression, expression To expression, Is comparisonoperator expression. The To keyword specifies a range of values. If you use the To keyword, the smaller value must appear before To. Use the Is keyword with comparison operators (except Is and Like) to specify a range of values. If not supplied, the Is keyword is automatically inserted.
statements-n Optional. One or more statements executed if testexpression matches any part of expressionlist-n.
elsestatements Optional. One or more statements executed if testexpression doesn't match any of the Case clause.


If testexpression matches any Case expressionlist expression, the statements following that Case clause are executed up to the next Case clause, or, for the last clause, up to End Select. Control then passes to the statement following End Select. If testexpression matches an expressionlist expression in more than one Case clause, only the statements following the first match are executed.

The Case Else clause is used to indicate the elsestatements to be executed if no match is found between the testexpression and an expressionlist in any of the other Case selections. Although not required, it is a good idea to have a Case Else statement in your Select Case block to handle unforeseen testexpression values. If no Case expressionlist matches testexpression and there is no Case Else statement, execution continues at the statement following End Select.

You can use multiple expressions or ranges in each Case clause. For example, the following line is valid:

Case 1 To 4, 7 To 9, 11, 13, Is > MaxNumber

Note   The Is comparison operator is not the same as the Is keyword used in the Select Case statement.

You also can specify ranges and multiple expressions for character strings. In the following example, Case matches strings that are exactly equal to everything, strings that fall between nuts and soup in alphabetic order, and the current value of TestItem:

Case "everything", "nuts" To "soup", TestItem

Select Case statements can be nested. Each nested Select Case statement must have a matching End Select statement.

This example uses the Select Case statement to evaluate the value of a variable. The second Case clause contains the value of the variable being evaluated, and therefore only the statement associated with it is executed.

Dim Number
Number = 8    ' Initialize variable.
Select Case Number    ' Evaluate Number.
Case 1 To 5    ' Number between 1 and 5, inclusive.
    Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8    ' Number between 6 and 8.
    Debug.Print "Between 6 and 8"
Case 9 To 10    ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else    ' Other values.
    Debug.Print "Not between 1 and 10"
End Select

Open in new window

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

lee555J5Author Commented:
@Ramanhp: I know I can do it that way, i.e., expand them out and check each condition individually. I am very familiar with that structure.
@dandraka: I mentioned in my OP I know about the Select...Case structure. I am very familiar with that as well.
I am hoping what I am specifically asking for is possible. I was having trouble crafting google search terms for it, but there may be a reason for that. It may not be possible. :-)
It gets tedious and wordy when I want to check a simple range but my If statement also includes a few Ands and Ors
BTW, dandraka, if intVar = 3, your second case will not be executed. ;-)
Patrick MatthewsCommented:
I agree with dandraka that Select Case makes a little more sense, but to use If:

If intVar > 0 And intVar < 4 Then
    'code for 1-3
ElseIf intVar < 7 Then
    'code for 4-6
ElseIf intVar = 7 Then
    'code for 7
ElseIf intVar <= 10 Then
    'code for 8-10
    'code for all other cases
End If

The above is all made up, of course :)
Mike EghtebasDatabase and Application DeveloperCommented:

Dim strArray(3) As String
Dim bolExists As Boolean
Dim i As Integer

strArray(0) = "Bob"
strArray(1) = "Beth"
strArray(2) = "Conrad"
strArray(3) = "Grant"
For i = 0 To UBound(strArray())
    If strArray(i) = "COnrad" Then bolExists = True
Next i
Mike EghtebasDatabase and Application DeveloperCommented:
strArray(3) could be a dynamic array.
I have applied it to string type here.
The If...Then can be revised to:

If strArray(i) = "Conrad" Then
  'it exist
  'run some code
  Exit sub
End if
lee555J5Author Commented:
@matthewspatrick: I know about the If...Then...Elseif...End If and the Select...Case...End Select structures. I was hoping for the ability for simple ranges. I know I can do this somewhat in SQL.
If x = [1-3] And y = [4,5] Then
is actually
If (x = 1 Or x = 2 Or x = 3) And (y = 4 Or y = 5) Then
This is easy with x and y but gets much more wordy with table.column and more complex conditionals. I was just looking for an easy range, but it looks as if this is not possible.
Thank you,
Patrick MatthewsCommented:

What you are asking for is not possible within the realm of VBA syntax.  You would need to use If...ElseIf...Else or Select Case as shown above.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lee555J5Author Commented:
"not possible" is exactly the answer I was looking for (and expecting), not the one I wanted, though.
Thank you,
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.