-1

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

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
pmelch
  • 101
  • 1
  • 3
  • 9
  • 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 '13 at 10:16
  • 1
    Access does not have a `case` AFAIK – juergen d Nov 15 '13 at 10:18
  • Even if it does or would, I have never known a case that works like this... – oerkelens Nov 15 '13 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 '13 at 10:24
  • @Ben - what do you know about what I have searched? – pmelch Nov 15 '13 at 10:37
  • Only that it is very little. `Case` doesn't look like that when it is supported, so you haven't looked that up. access doesn't support `case` so you haven't looked that up either. – Ben Nov 15 '13 at 10:53

1 Answers1

1

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.

Gord Thompson
  • 116,920
  • 32
  • 215
  • 418
  • 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 '13 at 07: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 '13 at 07:38