Selections (A2003)

[santosm]

Hi All,
I am looking for a better way to use an IF statement then using something like:
"if intAdmin = 1 or intAdmin = 2 or intAdmin = 3......... then"

How can I make the if statement look for 8 different numbers without using all those "OR"s?

Thanks,
Mark

[HansV]

In VBA, you can use Select Case:

Select Case intAdmin
Case 1 To 5, 12, 37
' some code here
...
Case 6, 9 To 11
' some other code here
...
Case Else ' all other values
' more code
...
End Select

[santosm]

Thanks Hans!

[santosm]

OK, one other thing.... How would you handle something where you want to select a rowsource for a combo box through code, looking for a list of let's say 8 values? Right now I use something like "select * from someTable where tech_id = 1 or tech_id = 2 or tech_id = 3......... Is there a better way to do this?

Thanks,
Mark

[HansV]

You can use

SELECT * FROM SomeTable WHERE TechID Between 1 And 8

or

SELECT * FROM SomeTable WHERE TechID In (1, 2, 4, 7, 11, 16, 22, 29)

[santosm]

Great!

[santosm]

Is it possible to do the same thing with text? Like......... In ("SomeText","Someother Text")....

Thanks,
Mark

[patt]

Try
WHERE Text in ("SomeText","Someother Text")

You cannot use the clause LIKE in this sense. LIKE is used with a wildcard.

[santosm]

Thanks!

[santosm]

Hi,
How would you handle a null then? I tried "Case Null" and it came back with invalid use of null.

Thanks,
Mark

[HansV]

Null is not a value, it signifies the absence of a value. You can't test for null in Select Case directly.

One option is to handle it separately:

If IsNull(Me.txtSomething) Then
...
Else
Select Case Me.txtDirection
Case "Left"
...
Case "Right"
...
End Select
End If

Another option is to handle it in the Case Else part. This is mostly useful if you can handle all possible values in the other Case parts:

Select Case Me.txtAmount
Case Is > 0
...
Case 0
...
Case Is < 0
...
Case Else
' Anything else, including Null
...
End Select