By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,872 Members | 2,333 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,872 IT Pros & Developers. It's quick & easy.

Access SQL version of CASE?

P: n/a
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

Aug 22 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Aug 22 '06 #2

P: n/a
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
Aug 23 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.