What is the correct CASE SELECT Statement in Access 2010?

Question

I am using vb.net and Access 2010 as the database (.accdb)

This works with MySQL:

SELECT user_id, username, first_name, middle_name, last_name,
CASE is_enable WHEN 1 THEN 'Yes' ELSE 'No' END 
FROM tbl_user_accounts ORDER BY user_id

But when passed the same query to Access, I get the following error:

Unrecognized keyword WHEN.

So I assume that the CASE Statement is different in access, or does access has that function at all?

P.S.
is_enable is boolean

There is no Case ... When. Switch function is an alternative. See **http://stackoverflow.com/questions/11534425/microsoft-access-case-query.** — chuff, Feb 09 '13 at 06:34
@chuff, how do you do something like CASE ELSE in the Switch function? — Ruben_PH, Feb 09 '13 at 06:38

score 17 · Accepted Answer · answered Feb 09 '13 at 07:06

17

Figured it out:
Access 2010 does not have the CASE function, we use SWITCH instead.

SELECT user_id, username, first_name, middle_name, last_name, SWITCH(is_enable=True,'Yes',is_enable=False,'No') FROM tbl_user_accounts ORDER BY user_id

Thanks to chuff and JW.

answered Feb 09 '13 at 07:06

Ruben_PH

1,692
7
25
42

4

Be aware that if none of your test conditions evaluate to True, the Switch function will return NULL. If you want to handle a default case where nothing matches, use: Switch(cond1, exp1, cond2, exp2, ..., True, expn). The last condition, True, will always match if none of the others did. So you'll always at least return a default value of expn. – Yawar Feb 10 '13 at 21:08
Thanks @Yawar, I am actually looking for something like the CASE ELSE function to handle results not in the CASE Selection. – Ruben_PH Feb 11 '13 at 04:52

score 10 · Answer 2 · edited May 30 '14 at 09:49

10

Alternatively, you can use IIF

IIF(is_enable = 1 , 'YES', 'NO')

edited May 30 '14 at 09:49

Álvaro González

142,137
41
261
360

answered Feb 09 '13 at 06:29

John Woo

258,903
69
498
492

same result @JW, still getting the Unrecognized WHEN error. – Ruben_PH Feb 09 '13 at 06:31
no, the `IIF` statement – John Woo Feb 09 '13 at 06:32
IIF works like a charm! Thanks JW. But I still need the CASE for future use of comparison with more than 2 values. – Ruben_PH Feb 09 '13 at 06:34
i have no access installed on my machine but i guess, `access` doens't support `cASE` statement. i can't find either on the docs. – John Woo Feb 09 '13 at 06:35
1

@paynet *"In Microsoft Access, the `Case statement can only be used in VBA code`."* [see here: MS Access (Case Statement)](http://www.techonthenet.com/access/functions/advanced/case.php) – John Woo Feb 09 '13 at 06:45
paynet, I've used IIF extensively as a workaround, both nested and with compound conditions, a la, IIF((field1 = "Y" or field2 = "Y) and field3 > 100, 1.75*field5, IIF(.... etc. For complex conditions. SWITCH I've always found a bit confusing to work with. As JW cites, CASE is available in VBA, but I've never had a need to work with it. – chuff Feb 09 '13 at 06:49
You will be a long tome looking for a boolean equal to 1 in MS Access. – Fionnuala Feb 09 '13 at 13:37
@Remou, I figured that one out, MS Access returns either true or false for a boolean type field. – Ruben_PH Feb 11 '13 at 04:51
Ms Access returns either **-1** or 0 for a true or false. – Fionnuala Feb 11 '13 at 10:12
At first, I tried checking for 1 or 0 for true and false respectively but it always returns false, then I tried using True and False as is (is_enable = True, is_enable = false) which actually works. – Ruben_PH Feb 11 '13 at 22:36

score 2 · Answer 3 · answered Feb 09 '13 at 20:47

2

Choose is another Access (really VBA) function available in queries, similar to Switch but returns an index to a list of answers. For instance, Choose([Choice], "A","B","C") would return "B" if Choice was 2. I've found it useful in the past.

answered Feb 09 '13 at 20:47

Peter Lake

338
1
4

I'll keep this handy. Thanks @Peter – Ruben_PH Feb 11 '13 at 04:53

Fionnuala · Answer 4 · 2013-02-09T13:39:27.883

1

This is really a display issue, and would possibly be best done somewhere other than SQL. is_enabled is clearly a Boolean/YesNo data type, so you can just format it:

 SELECT Format(AYesNo,'Yes/No') As ATextYN FROM table1

Or

 Format(AYesNo,'True/False')
 Format(AYesNo,'On/Off')

All work in VB.Net and return text, not boolean.

See http://msdn.microsoft.com/en-us/library/office/gg251755.aspx

edited Feb 09 '13 at 13:39

answered Feb 09 '13 at 12:08

Fionnuala

90,370
7
114
152

I tested this and it also works. But my purpose for using CASE Statement, which is now SWITCH Statement in Access is to be able to compare more than 2 values and not just boolean alone. It just happens that my example was boolean. On the other hand, just like what chuff said, the SWITCH Statement in Access is proving to be confusing to work with. Nonetheless, thanks for the answer, I didn't know such a function exist. I'll keep this handy. – Ruben_PH Feb 09 '13 at 16:53

score 0 · Answer 5 · answered Feb 09 '13 at 06:35

0

Try this

yourColumnName=case when is_enable=1 then 'Yes' ELSE 'No' END

answered Feb 09 '13 at 06:35

polin

2,745
2
15
20

same result @polin, still getting the Unrecognized WHEN error. – Ruben_PH Feb 09 '13 at 06:37
you need to give a column name of your table in "yourColumnName". – polin Feb 09 '13 at 06:40
Yes I did that. I'm looking into @chuff's answer. Seems like Access does not have the CASE function. – Ruben_PH Feb 09 '13 at 06:42
oh. thats right. I didn't notice that you are using Access. Its possible in sql server – polin Feb 09 '13 at 06:43

What is the correct CASE SELECT Statement in Access 2010?

5 Answers5

Linked