Select Case Statement Not Working When Specifying Ranges

[John]

Here's a code sample that recreates a problem I've
encountered with the Select Case statement. The value
does not trigger the appropriate Case statement. Any
ideas?

Many thanks!



Function fnTestSelect() As Integer
Dim dblValue As Double

dblValue = 25.1

Select Case dblValue
Case Is <= 10
MsgBox "<= 10"
Case Is > 10 And dblValue <= 15
MsgBox "> 10 and <= 15"
Case Is > 15 And dblValue <= 20
MsgBox "> 15 and <= 20"
Case Is > 20
MsgBox "> 20"
End Select

End Function

 

[Graham Mandeno]

Hi John

The syntax for a Case statement involving a range is:
Case num1 To num2

So you need:

Select Case dblValue
Case Is <= 10
MsgBox "<= 10"
Case 10 To 15
MsgBox "> 10 and <= 15"
Case 15 To 20
MsgBox "> 15 and <= 20"
Case Is > 20
MsgBox "> 20"
End Select

 

[Mark A. Sam]

John

I am just guessing now, but two possibilities are:

1) Remove the dblValue reference

10 And dblValue <= 15 to
10 And <= 15

2) Use the Between operator
Between 11 and 15

If niether of those works, try chaning again using a decimal form of the
numbers:
Between 11.00 and 15.00

God Bless,

Mark A. Sam

 

[fredg]

Here's a code sample that recreates a problem I've
encountered with the Select Case statement. The value
does not trigger the appropriate Case statement. Any
ideas?

Many thanks!



Function fnTestSelect() As Integer
Dim dblValue As Double

dblValue = 25.1

Select Case dblValue
Case Is <= 10
MsgBox "<= 10"
Case Is > 10 And dblValue <= 15
MsgBox "> 10 and <= 15"
Case Is > 15 And dblValue <= 20
MsgBox "> 15 and <= 20"
Case Is > 20
MsgBox "> 20"
End Select

End Function

John,

The syntax for Select case is shown in Access Help.

Try it this way.....
Function fnTestSelect() As Integer
Dim strMessage As String
Dim dblValue As Double
dblValue = 25.1
Select Case dblValue
Case Is <= 10
strMessage = "<= 10"
Case 10 To 15
strMessage = ">10 and <=15"
Case 15 To 20
strMessage = ">15 and <=20"
Case Is > 20
strMessage = ">20"
End Select
MsgBox strMessage
End Function

 

[Steve Conway]

Hi John,

The Select Case will execute until the *first* true is encountered, so you
could use:

Function fnTestSelect() As Integer
Dim dblValue As Double
dblValue = 25.1
Select Case dblValue
Case Is > 20
MsgBox "> 20"
Case Is > 15
MsgBox "> 15 and <= 20"
Case Is > 10
MsgBox "> 10 and <= 15"
Case Else
MsgBox "<= 10"
End Select

End Function

HTH
Steve C