Results 1 to 6 of 6
  1. #1
    rameshjctr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Posts
    16

    syntax error in insert into


    this is my first coding in access. i am getting syntax error in INSERT INTO command. i tried to sort it out my way. but i am at loss. can eny one help me.

    thanking you
    RAMESH J

    Private Sub cmdadd_Click()
    Dim sSQL As String

    sSQL = " INSERT INTO empd (PAN,PEN,ENAME,DESIG,ADDR,ONAME,PLACE,DOB,DOJ,BP,G PF,SLI,GIS,LIC,FBS,SOP) VALUES ( " & Me!Text1 & " , " & Me!Text2 & " , '" & Me!Text3 & "' , '" & Me!List1 & "' , '" & Me!Text4 & "' , '" & Me!List2 & "' , '" & Me!List3 & "' , #" & Me!Text5 & "# , #" & Me!Text6 & "# , " & Me!List4 & " , " & Me!Text7 & " , " & Me!Text8 & " , " & Me!Text9 & " , " & Me!Text10 & " , " & Me!Text11 & " , '" & Me!List5 & "'); "
    DoCmd.RunSQL sSQL
    Me.Refresh

    End Sub

  2. #2
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    First, don't use special characters like space, dash, etc. in field names (G PF) and if you do, enclose them in brackets [].
    Second - see how your sSQL string looks like in immediate window (Ctrl+G and type "? sSQL" without quotes and press Enter). If you won't spot error in string there please copy it and paste here. It's hard to analyse it without knowing values in all of your textX, listX etc. controls.

  3. #3
    rameshjctr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Posts
    16
    INSERT INTO empd (PAN,PEN,ENAME,DESIG,ADDR,ONAME,PLACE,DOB,DOJ,BP,G PF,SLI,GIS,LIC,FBS,SOP) VALUES ( " & Me!Text1 & " , " & Me!Text2 & " , '" & Me!Text3 & "' , '" & Me!List1 & "' , '" & Me!Text4 & "' , '" & Me!List2 & "' , '" & Me!List3 & "' , #" & Me!Text5 & "# , #" & Me!Text6 & "# , " & Me!List4 & " , " & Me!Text7 & " , " & Me!Text8 & " , " & Me!Text9 & " , " & Me!Text10 & " , " & Me!Text11 & " , '" & Me!List5 & "');


    values i gave 12345,ABCDE1254W,CHARLIE,COMPUTER HARDWARE,INDIA,XYZ OFFICE,INDIA,12/12/1999,12/12/1999,17420,80,50,42,12,0,1234-2134

  4. #4
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Ok, I shouldn't do all the work for you, but I got too much time on my hands today, so...
    Your sSQL string will look like:
    Code:
    " INSERT INTO empd (PAN,PEN,ENAME,DESIG,ADDR,ONAME,PLACE,DOB,DOJ,BP,G PF,SLI,GIS,LIC,FBS,SOP) 
    VALUES ( 12345 , ABCDE1254W , 'CHARLIE' , 'COMPUTER HARDWARE' , 'INDIA' , 'XYZ OFFICE' , 'INDIA' , #12/12/1999# , #12/12/1999# , 17420 ,
     80 , 50 , 42 , 12 , 0 , '1234-2134'); "
    1. Your [G PF] field contains space in its name
    2. You're updating [PEN] field with string ABCDE1254W without quotes around it
    3. If you seek advice on forums it's in good tone to try, what was suggested maybe?

  5. #5
    rameshjctr is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Feb 2016
    Posts
    16
    thank you cyanidem. actually the field name GPF doesnt have a space in between. yes i was using pan value as numeric instead of alphanumeric. it was a mistake. i corrected the mistakes. still i am getting the same error message. ok i will try. thanks for your help.

  6. #6
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Can you copy/paste value of your sSQL string after your code breaks on syntax error? Use Immediate Window as suggested in post #2.

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

Similar Threads

  1. Syntax Error In Insert Into Statement
    By ULMA in forum Access
    Replies: 32
    Last Post: 02-24-2015, 06:00 PM
  2. Replies: 3
    Last Post: 03-05-2013, 11:17 AM
  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