Access SQL version of CASE?

[philbrierley]

What is the easiest way to write SQL that Access understands to achieve
simple standard SQL query below?

Thanks in Advance,

Phil
SELECT

charA,

CASE
WHEN charA < -199 THEN 2
WHEN charA < 31 THEN 3
WHEN charA < 82 THEN 4
WHEN charA < 100 THEN 5
WHEN charA < 105 THEN 6
WHEN charA < 111 THEN 7
WHEN charA < 143 THEN 8
WHEN charA < 165 THEN 9
WHEN charA < 233 THEN 10
WHEN charA >= 233 THEN 11
END AS Bin_charA

FROM X

[Bri]

ph**********@hotmail.com wrote:

What is the easiest way to write SQL that Access understands to achieve
simple standard SQL query below?

Thanks in Advance,

Phil
SELECT

charA,

CASE
WHEN charA < -199 THEN 2
WHEN charA < 31 THEN 3
WHEN charA < 82 THEN 4
WHEN charA < 100 THEN 5
WHEN charA < 105 THEN 6
WHEN charA < 111 THEN 7
WHEN charA < 143 THEN 8
WHEN charA < 165 THEN 9
WHEN charA < 233 THEN 10
WHEN charA >= 233 THEN 11
END AS Bin_charA

FROM X

Take a look at the VBA Switch() function. Access allows you to use
functions in a query. So, some air code:

SELECT charA, Switch(charA < -199, 2, charA < 31, 3,charA >= 233, 11) AS
Bin_charA FROM X

[salad]

ph**********@hotmail.com wrote:

What is the easiest way to write SQL that Access understands to achieve
simple standard SQL query below?

Thanks in Advance,

Phil
SELECT

charA,

CASE
WHEN charA < -199 THEN 2
WHEN charA < 31 THEN 3
WHEN charA < 82 THEN 4
WHEN charA < 100 THEN 5
WHEN charA < 105 THEN 6
WHEN charA < 111 THEN 7
WHEN charA < 143 THEN 8
WHEN charA < 165 THEN 9
WHEN charA < 233 THEN 10
WHEN charA >= 233 THEN 11
END AS Bin_charA

FROM X

As noted, Switch can be used. Switch is a function. If working with
A97, you'd use a function. Switch is a function, so if switch() isn't
available, you'd roll your own. Create a new column in the query. Ex:
Rank : GetRank([CharA])

Function GetRank(varC As Variant) As Integer
Select Case VarC
Case <-199
....
Case Else
GetRank = 0
End Select
End Function