Results 1 to 4 of 4

Thread: Insert Problem

  1. #1
    Join Date
    May 2014
    Posts
    16

    Unanswered: Insert Problem

    What is wrong with this SQL insert statement. I get a compiler error: Syntax on the zSQL INSERT statement

    Dim SupplierID As Integer
    Dim typex As String
    Dim RatingPeriod As String
    Dim RatingDate As Date
    Dim AppLevel As String

    SupplierID = resulti
    typex = "ED"
    RatingPeriod = Me.fFYQtr.Value
    RatingDate = Me.fDate.Value
    AppLevel = "Lock"

    'construct record
    zSQL = _
    "INSERT INTO [dbo_DB02003] " & _
    "(SupplierID, Key, Type, Ratingperiod, ratingdate, applevel" & _
    " VALUES(" & _
    "'" & supplierid & "'" & _
    ", '" & key & "'" & _
    ", '" & type & "'" & _
    ", '" & ratingperiod & "'" & _
    ", '" & ratingdate & "'" & _
    ", '" & applevel & "')"

    'Debug.Print zSQL

    DoCmd.SetWarnings False
    DoCmd.RunSQL zSQL

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    who knows
    as you haven't provided the data types
    nor an example of what you are trying to insert
    yet you have commented out the debug line which may have provided us with a clue

    however I'd hazard a guess at:-
    numeric values shouln't be delimited
    alphanumerics must be delmited with a pair of either ' or "
    date values must be delimited by a # and be in either ISO format (#yyyy/mm/dd#) or US format (#mm/dd/yyyy#)

    you'd proabbly get a better of idea of why the code is failing if you didn't turn off the warnings beforfe tryng to run the failing code....


    ...but apart form that....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2005
    Posts
    119
    I second Healdem's comment. I might add that if the supplier id is a numeric field, it would not require the quotes. Have you gotten this working?

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    And you never closed off the parentheses in the INSERT INTO clause.
    Paul

Tags for this Thread

Posting Permissions

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