Results 1 to 8 of 8
  1. #1
    Eranka is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    150

    Syntax Error In Insert Into Statement

    Hi Guys



    Code:
     CurrentDb.Execute "INSERT INTO Payment_Details (Lecturer_ID,Unit_ID,Class_Date,Class_Type,Level_Of_Unit,Session_ID,Payment_Period_ID,Time_In,Time_Out) " & "VALUES ('" & Me.Combo287 & "','" & Me.Combo301 & "','" & Me.Text297 & "','" & Me.Text214 & "','" & Me.Combo309 & "',,'" & Me.Combo245 & "','" & Me.Text233 & "','" & Me.Text235 & "','" & Me.Text237 & "','" & Me.Text320 & "')"
    I tried above insert into statement, but when i click on this button i get the error "Syntax error in INSERT INTO Statement". Any solutions to overcome this error?

    Thanks In Advance

  2. #2
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,756
    Are all of the fields text as you have used text delimiters in each case.
    If any are numbers use: " & numberfield & "
    For date fields use: #" & datefield & "#

    Also add a space after each comma and replace the double comma after combo309
    Finally strongly recommend you give your controls meaningful names are relate to the field e.g cboUnit
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    50,036
    Agree, except spaces after commas not important.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    Eranka is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    150
    Hi

    thanks guys. That worked.

    Code:
     CurrentDb.Execute "INSERT INTO Lec_Details (Lecturer_ID,Lecturer_Name,Highest_Qualification,Landline,Mobile,Email,Address) " & "VALUES ('" & Me.Text1 & "','" & Me.Text2 & "','" & Me.Text3 & "','" & Me.Text49 & "','" & Me.Text52 & "','" & Me.Text55 & "','" & Me.Text58 & "')"
    with the above code im getting syntax error message. any solutions? i tried to find but couldn't.

  5. #5
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,756
    Spaces not required after commas but make SQL easier to read.
    Also space IS required between each section in any query e.g. Before VALUES (as done here) or WHERE or ORDER BY etc
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  6. #6
    Eranka is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2017
    Posts
    150
    Hey

    is it the reason for syntax error message?

  7. #7
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    4,756
    I was answering June's point

    The double comma was definitely wrong in the first post.
    One again, check your delimiters depending on field type
    The other points raised are recommendations.
    Colin (Mendip Data Systems): Website, email
    Try again. Fail again. Fail better.
    A
    sking for help isn't giving up. Its refusing to give up.

  8. #8
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,958
    also recommend you assign the code to a string and then debug.print so you can see what you have constructed before executing - you can also copy paste the resultant code into a new query to check it will execute as required

    dim sqlstr as string
    sqlstr="INSERT INTO....
    debug.print sqlstr
    currentdb.execute sqlstr

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

Similar Threads

  1. Syntax error in INSERT INTO statement
    By darkwind in forum Programming
    Replies: 7
    Last Post: 05-09-2017, 01:43 AM
  2. Syntax Error In Insert Into Statement
    By ULMA in forum Access
    Replies: 32
    Last Post: 02-24-2015, 06:00 PM
  3. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  4. Syntax Error: Insert Into statement in VBA
    By Kimbertha in forum Programming
    Replies: 1
    Last Post: 07-24-2012, 05:02 PM
  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