的MS Access VBA SQL查询调试选择的情况下 [英] MS Access VBA SQL query debugging select case

问题描述

在MS Access 2013 VBA我得到这个SQL字符串语法错误：

In MS Access 2013 VBA I get a syntax error in this SQL-string:

strSQL = "INSERT INTO [man_year] ( man_year_val, year_int, main_research_area, organisation, man_year_source ) SELECT KU.[2007], '2007' AS Expr1, " _
& "select case right(left(KU.man_year_source;6);2) like 'Hu' 3 case right(left(KU.man_year_source;6);2) like 'Sa' 1 case right(left(KU.man_year_source;6);2) like 'Te' 2 case right(left(KU.man_year_source;6);2) like 'Su' 4 case right(left(KU.man_year_source;6);2) like 'Ud' 5 AS Expr2, " _
& "4 AS Expr3, " _
& "select switch" _
& "(left(KU.man_year_source;3) like '1. '; 1;" _
& "left(KU.man_year_source;3) like '1.1'; 4;" _
& "left(KU.man_year_source;3) like '1.2'; 5;" _
& "left(KU.man_year_source;3) like '1.3'; 6;" _
& "left(KU.man_year_source;3) like '1.4'; 7;" _
& "left(KU.man_year_source;3) like '1.5'; 8;" _
& "left(KU.man_year_source;3) like '1.6'; 9;" _
& "left(KU.man_year_source;3) like '2. '; 2;" _
& "left(KU.man_year_source;3) like '2.1'; 47;" _
& "left(KU.man_year_source;3) like '2.2'; 48;" _
& "left(KU.man_year_source;3) like '2.3'; 49;" _
& "left(KU.man_year_source;3) like '2.4'; 50;" _
& "left(KU.man_year_source;3) like '2.5'; 51;" _
& "left(KU.man_year_source;3) like '2.6'; 52;" _
& "left(KU.man_year_source;3) like '3. '; 3;" _
& "left(KU.man_year_source;3) like '3.1'; 53;" _
& "left(KU.man_year_source;3) like '3.2'; 54;" _
& "left(KU.man_year_source;3) like '3.3'; 55;" _
& "left(KU.man_year_source;3) like '3.4'; 56;" _
& "left(KU.man_year_source;3) like '3.5'; 57;" _
& "left(KU.man_year_source;3) like '3.6'; 58) from KU;"

我得到的情况下，部分的错误，但可能是因为未达到开关部分呢。 :-)任何人都可以请大家帮忙，我找不到错误。​​

I get the error in the CASE-part, but that might be because it hasn't reached the SWITCH-part yet. :-) Can anyone please help, I cannot find the error.

最佳pmelch

推荐答案

我看到你的SQL语句至少有两个问题：

I see at least two issues with your SQL statement:

首先，访问SQL不支持 CASE 关键字。如果你正在考虑的 CASE ...当结构中的 T-SQL （微软的SQL Server），那么相当于在访问SQL是开关（）函数（参考：的这里）。你可以把开关（）的功能做

First, Access SQL does not support the CASE keyword. If you were thinking of the CASE ... WHEN construct in T-SQL (Microsoft SQL Server) then the equivalent in Access SQL is the Switch() function (ref: here). You can think of the Switch() function as doing

Switch(when1, then1, when2, then2, ...)

第二，据我所知访问SQL只支持期（。）作为小数点符号和逗号（，）作为列表分隔符，即使你的机器上的区域设置指定其他值（例如，逗号（，）作为小数点符号和分号（; ）作为列表分隔符）。换句话说，我相当肯定的是，

Second, as far as I know Access SQL only supports period (.) as the decimal symbol and comma (,) as the list separator, even if the Regional Settings on your machine specify other values (e.g., comma (,) as the decimal symbol and semi-colon (;) as the list separator). In other words, I'm fairly certain that

left(KU.man_year_source;3)

行不通;你需要使用

will never work; you'll need to use

left(KU.man_year_source,3)

来代替。

这篇关于的MS Access VBA SQL查询调试选择的情况下的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！