Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    5

    Unanswered: Update SQL Table through VBA Date Problem

    sSQL1 = "INSERT INTO it_inv_audit_log (log_date, inv_id, user_id, comments) VALUES ((" & MyDate & ",""" & Me.teamdfw_inv_id1 & """,""" & tempuserid & """,""" & comments & """)"

    tempid = DLookup("TeamDFWID", "tblUsers", "UserID= '" & Me.AssignedBy & "'")
    MyDate = Format(Now, "yyyy-mm-dd " & "hh:mm:ss.n")

    when excute ssql1 get error message "Line 1:Incorrect syntax near '08'.

    the log_date column on the sql table is datetime format so not sure where the problem is. the problem seems to be coming from the hour, i guess it doesnt like the space in front of it??

    thanks in advance
    Last edited by dabomb311; 08-24-10 at 11:24.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what does the text you are sending the SQL engine look like

    the error message tells you that the SQL is complaining about your code near the characters '08'

    is the log value 'just' a time stamp... does it have to be a specific point in time, or is the time it was entered into the db sufficient for your purposes? if so I'd replace the date time bit with now() .. the system time.
    eg
    VALUES ((" & MyDate
    becomes
    VALUES (" & now()
    or
    VALUES (#" & now() & "#
    first off dates in JET SQL should be in the US format mm/dd/yyyy HH:MMS, and dates should be encapsulated with the # symbol

    eg
    #08/24/2010 16:05:06#
    you can specify dates in ISO format yyyy/mm/dd HH:MMS,
    but not proper format dd/mm/yyyy
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Aug 2010
    Posts
    5
    the log value is just a time stamp... just the time it was entered into the database

    with the now() - I get the same error... i changed the code to this:

    SSQL1 = "INSERT INTO it_inv_audit_log (log_date, inv_id, user_id, comments) VALUES '" & Now() & "' & '" & Me.teamdfw_inv_id1 & "' & '" & tempid & "' & '" & comments & "'"

    and get the folloing error Line 1:incorrect syntax near '8/25/2010 8:34:40 AM'

    is it complaing because of the format? in the table the the log_date column is in the datetime datetype and when i look at the data it looks like this:
    2010-08-25 08:29:25.517

    thanks for your help? if i need to add the #'s? can you help me with adding those to the above code.


    i did get the following sql code to work and it looks correct
    ----------------------------------------------------------
    INSERT INTO [dals].[dbo].[it_inv_audit_log]
    ([log_date]
    ,[inv_id]
    ,[user_id]
    ,[comments])
    VALUES
    ('2001-08-25 08:53:41.517' ,'8375','5862','Record updated (status_id: 2 >> 1; )')
    GO
    Last edited by dabomb311; 08-25-10 at 10:55. Reason: updating sql statement

  4. #4
    Join Date
    Aug 2010
    Posts
    5
    i got it... YEA!!!!

    too many & needed , instead and needed ( )

    SSQL1 = "INSERT INTO it_inv_audit_log (log_date, inv_id, user_id, comments) VALUES ('" & MyDate & "' , '" & Me.teamdfw_inv_id1 & "' , '" & tempid & "' , '" & comments & "');"

Posting Permissions

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