SQL Access 2010 中正确的 CASE SELECT 语句是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14785586/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the correct CASE SELECT Statement in Access 2010?
提问by Ruben_PH
I am using vb.net and Access 2010 as the database (.accdb)
我使用 vb.net 和 Access 2010 作为数据库 (.accdb)
This works with MySQL:
这适用于 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:
但是当将相同的查询传递给 Access 时，我收到以下错误：
Unrecognized keyword WHEN.
So I assume that the CASE Statement is different in access, or does access has that function at all?
所以我假设 CASE 语句在访问上是不同的，或者访问是否有这个功能？
P.S.
is_enable is boolean
PS
is_enable 是布尔值
回答by Ruben_PH
Figured it out:
Access 2010 does not have the CASE function, we use SWITCH instead.
想通了：
Access 2010 没有 CASE 功能，我们使用 SWITCH 代替。
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
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.
感谢 chuff 和 JW。
回答by John Woo
回答by Peter Lake
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.
选择是查询中可用的另一个 Access（真正的 VBA）函数，类似于 Switch，但返回一个指向答案列表的索引。例如，如果 Choice 为 2，Choose([Choice], "A","B","C") 将返回 "B"。我过去发现它很有用。
回答by Fionnuala
This is really a display issue, and would possibly be best done somewhere other than SQL. is_enabledis clearly a Boolean/YesNo data type, so you can just format it:
这确实是一个显示问题，最好在 SQL 以外的其他地方完成。is_enabled显然是 Boolean/YesNo 数据类型，所以你可以格式化它：
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.
所有在 VB.Net 中工作并返回文本，而不是布尔值。
See http://msdn.microsoft.com/en-us/library/office/gg251755.aspx
请参阅http://msdn.microsoft.com/en-us/library/office/gg251755.aspx
回答by polin
Try this
尝试这个
yourColumnName=case when is_enable=1 then 'Yes' ELSE 'No' END
