Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    New Zealand
    Posts
    2

    Unanswered: Insert query syntax error

    I am trying to set up some code to add records to a linking table based on a couple of parameters from the form, using VB in Access Form, SQL backend.
    I created the linking table (Diary_tb) from SQL server as:
    NUMBER nvarchar 8 primary key
    DETAILS nvarchar 50 allowed null
    STARTDATE smalldatetime 4 primary key
    FINISHDATE smalldatetime 4 allowed null
    TYPE nvarchar 20 primary key

    My code is:
    Private Sub RNKINDX_AfterUpdate()
    Dim strMsg As String
    Dim strInput As String
    Dim dteStart As Date
    Dim strDetail
    Dim STARTDATE As Date
    strMsg = "Do you want to promote the new rank ?"
    If MsgBox(strMsg, vbOKCancel, "Error!") = vbOK Then
    newRank = Me![RANK]
    strInput = InputBox("Please input promotion date")

    STARTDATE = CDate(strInput)
    strDetail = oldRank + newRank

    Set dbs = CurrentDb
    strSQL = "INSERT INTO DIARY_tb (NUMBER, DETAILS, STARTDATE, TYPE) VALUES ('" & Trim(Me![NUMBER]) & "','" & strDetail & "', #" & STARTDATE & "#, 'PROMOTION');"
    DoCmd. RunSQL strSQL
    Else
    Exit Sub
    End If
    End Sub

    After I run this code I got error message:
    Run-time Error 3134
    Syntax error in INSERT INTO statement.

    Many thanks for any help.

    Cathy

  2. #2
    Join Date
    Dec 2003
    Posts
    454
    Try this. I removed ";" at the end of the query.

    strSQL = "INSERT INTO DIARY_tb (NUMBER, DETAILS, STARTDATE, TYPE) VALUES ('" & Trim(Me![NUMBER]) & "','" & strDetail & "', #" & STARTDATE & "#, 'PROMOTION')"

  3. #3
    Join Date
    Jan 2004
    Location
    New Zealand
    Posts
    2
    This doesn't work. I tried to remove ";", but still not work.
    Any good suggestion????

  4. #4
    Join Date
    Dec 2003
    Posts
    454
    What is the data type of Number? If it is not string, you may remove "'".

    strSQL = "INSERT INTO DIARY_tb (NUMBER, DETAILS, STARTDATE, TYPE) VALUES (" & Me![NUMBER] & ",'" & strDetail & "', #" & STARTDATE & "#, 'PROMOTION')"

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    If you get your data from TextBoxs intered by the user in a Form then try:


    strSQL = "INSERT INTO DIARY_tb (NUMBER, DETAILS, STARTDATE, TYPE) VALUES (" & Me!NUMBER & ",'" & strDetail & "', #" & STARTDATE & "#, CDATE(PROMOTION))"


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •