Results 1 to 2 of 2
  1. #1
    Kimbertha is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Sep 2010
    Posts
    33

    Syntax Error: Insert Into statement in VBA

    Hello All:



    It's been two years since I had my last serious Access challenge . . .

    I am currently struggling with an Insert Into command in Visual Basic.

    Here is the SQL from my query. This works just fine.

    INSERT INTO [Data Summary] ( Store, Description, [Period 1], [Period 2], [Period 3], [Period 4], [Period 5], [Period 6], [Period 7], [Period 8], [Period 9], [Period 10], [Period 11], [Period 12], [Year to Date] )
    SELECT "KS006" AS Store, KS006.F1 AS Description, KS006.[Period 1], KS006.[Period 2], KS006.[Period 3], KS006.[Period 4], KS006.[Period 5], KS006.[Period 6], KS006.[Period 7], KS006.[Period 8], KS006.[Period 9], KS006.[Period 10], KS006.[Period 111] AS [Period 11], KS006.[Period 12], KS006.[Year to Date]
    FROM KS006
    WHERE (((KS006.[Year to Date]) Is Not Null));


    Here is what I built using the Macro Tools in Access 2010. This returns the "Syntax error in Insert Into" error message.


    DoCmd.RunSQL "INSERT INTO [Data Summary] ( Store, Description, [Period 1], [Period 2], [Period 3], [Period 4], [Period 5], [Period 6], [Period 7], [Period 8], [Period 9], [Period 10], [Period 11], [Period 12], [Year to Date] )"
    DoCmd.RunSQL "SELECT ""KS006"" AS Store, KS006.F1 AS Description, KS006.[Period 1], KS006.[Period 2], KS006.[Period 3], KS006.[Period 4], KS006.[Period 5], KS006.[Period 6], KS006.[Period 7], KS006.[Period 8], KS006.[Period 9], KS006.[Period 10], KS006.[Period 111] AS [Period 11], KS006.[Period 12], KS006.[Year to Date]"
    DoCmd.RunSQL "FROM KS006"
    DoCmd.RunSQL "WHERE (((KS006.[Year to Date]) Is Not Null))", -1


    I've been poking around with the syntax and cannot get it right!

    Regards,

    Kimberly

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    21,712
    In the interests of learning how to fish, here's a good method to use to debug the SQL:

    http://www.baldyweb.com/ImmediateWindow.htm

    I see a couple of issues offhand. You have to build a single string variable and execute it. You're executing 3 incomplete strings.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 5
    Last Post: 03-30-2012, 09:00 AM
  2. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  3. INSERT INTO Syntax Error
    By eww in forum Programming
    Replies: 1
    Last Post: 04-06-2011, 10:28 AM
  4. SQL INSERT INTO Date Syntax Error
    By tuna in forum Programming
    Replies: 5
    Last Post: 08-10-2010, 06:17 AM
  5. Syntax Error In Insert Into Statement
    By frankvfox in forum Queries
    Replies: 1
    Last Post: 09-09-2008, 12:35 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums