Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote -1 down vote favorite

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.

Best pmelch

share|improve this question
1  
from what i can make out (please format yuor post) yuo have several SELECT clauses following one-another. That is not going to work. You do not need SELECT before each CASE –  oerkelens Nov 15 at 10:16
1  
Access does not have a case AFAIK –  juergen d Nov 15 at 10:18
 
Even if it does or would, I have never known a case that works like this... –  oerkelens Nov 15 at 10:21
1  
Is SO really the first port of call now? Do people not even try to search their own documentation or examples before coming here? –  Ben Nov 15 at 10:24
 
@Ben - what do you know about what I have searched? –  pmelch Nov 15 at 10:37
show 1 more comment

1 Answer

up vote 1 down vote accepted

I see at least two issues with your SQL statement:

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, ...)

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)

instead.

share|improve this answer
 
Thanks Gord for your answer! It works for me now. When there are many different errors involved it may be difficult to do debugging. I found sites that say CASE will work in Access SQL and others that say CASE won't work, so for me it was trial and error. –  pmelch Nov 18 at 7:36
 
And then I found that my long list of when/then in the switch statement won't work. It is better to have a table with these conditions and the refer to that table. Anyhow - thnx for a non-arrogant answer Gord. –  pmelch Nov 18 at 7:38
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.