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 MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    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 MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  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 MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    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, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    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