inconsistent behavior IFTHEN vs SelectCase

[Rob Konikoff]

I have two snippets of code used in Microsoft Access 2003 VBA. It takes and integer and checks to see if it is in one of five groups; less than 2, =2, 3 or 4, and 5 or greater. Using IF-THEN-ELSE works, but Select case doesn't!

Here are the snippets:
' age_chk is an integer
' AgeGroup is a variant
' This one works...
if age_chk < 2 then
AgeGroup = 1
else if age_chk = 2
AgeGroup = 2
else if age_chk = 3 then
AgeGroup = 3
else if age_chk = 4 then
AgeGroup = 4
Else AgeGroup = 5
end if
' This one doesn't...
Select Case age_chk
Case age_chk < 2
AgeGroup = 1
Case age_chk = 2
AgeGroup = 2
Case age_chk = 3
AgeGroup = 3
Case age_chk = 4
AgeGroup = 4
Case Else
AgeGroup = 5
End Select
' end snippets

[debasisdas]

it never happens that if--ehen and case will return different result.

[debasisdas]

can u kindly post what is the difference in the result between the two.

[AV Manoharan]

Dear Rob,
The syntax of the case stmnt is not that what you have used. Though a if -else stmnt can be substituted for a case stmnt and vise versa, an assignment with in the case stmnt. has to be as follows. This is because a case stmnt will return a single value the one corresponds to the first WHEN clause that is true.

SELECT AgeGroup=

CASE Age_Chk
When 1 then 1
when 2 then 2
when 3 then 3
when 4 then 4
else 5
END

[Rob Konikoff]

I tried several variations, but the bottom line is that WHEN and THEN key words cause errors...

Select Case age_chk
when age_chk < 2 then AgeGroup = "0-1 Day"

This is MS VBA 6.3 in Access 2003.

Thanks.

[AV Manoharan]

I tried several variations, but the bottom line is that WHEN and THEN key words cause errors...

Select Case age_chk
when age_chk < 2 then AgeGroup = "0-1 Day"

This is MS VBA 6.3 in Access 2003.

Thanks.

The WHEN and THEN clause works as TSQL statements only
For pure VB the syntax is as follws:

Select [ Case ] testexpression
[ Case expressionlist
[ statements ] ]

[ Case Else
[ elsestatements ] ]
End Select
Expressionlist represents match values for your testexpression
It can have multiple values seperated by comas or a range by using the keyword TO
eg.
CASE 1 TO 5
or
CASE 1,2,3,4,5
if you are using comparison, you have to Use the Is keyword
eg.
CASE Is < 2

[QVeen72]

Hi Rob,

ur Syntax for "Select-Case" is wrong.
In ur code, the Boolean Result of "Age_Chk<2"
Considered for Select Case.

Check this Syntax

Select Case Age_Chk
    Case [B]Is < 2[/B]
        AgeGroup = 1
    Case 2
        AgeGroup = 2
    Case 3
        AgeGroup = 3
    Case 4
        AgeGroup = 4
    Case Else
        AgeGroup = 5
End Select

:)
REgards
Veena