Member Avatar for 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

  • 4 Contributors
  • 6 Replies
  • 118 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by QVeen72

Dani AI

Generated

Short answer: the Select Case failed because the Case lines were written as boolean tests but the statement was comparing the numeric test value to those boolean results. That subtle evaluation/typing behaviour is why your If/ElseIf version worked while the literal Select Case did not.

VBA evaluates a Case expression before it is compared to the Select Case testexpression. If you write a comparison inside a Case (for example a < b), that comparison yields a Boolean. When VBA coerces that Boolean to a number for comparison, True becomes -1 and False becomes 0. So comparing an integer like 1 to a Boolean result will not match in the way you expect — the integer must equal -1 or 0 to hit that Case.

Fix options

  • Keep the working If/ElseIf; it is explicit and clear.
  • Change the Select Case pattern so the Case expressions themselves are comparisons against a fixed True test (often called the "Select Case True" pattern), which causes the Case lines to be evaluated as boolean values and matched against True.
  • Use the Select Case forms that compare the testexpression directly to value lists or ranges (or the VB comparison form) so you are matching the numeric testexpression against values rather than against booleans.

Practical checks and cautions

  • Turn on Option Explicit and declare types so unexpected Variants/Nulls do not hide problems.
  • Verify the runtime type and value (for example with Debug.Print TypeName(...) and Debug.Print ...) to make sure age_chk is numeric and not Null or a string.
  • In Access, guard against Nulls (use IsNull or Nz) before running comparisons.
  • Remember SQL CASE/WHEN syntax (WHEN/THEN) is different from VBA Select Case; a SQL-style example will not parse in VBA, as and noted.

Recommended Answers

All 6 Replies

Member Avatar for debasisdas

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

Member Avatar for debasisdas

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

Member Avatar for 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

Member Avatar for 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.

Member Avatar for 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

Member Avatar for 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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.