Results 1 to 6 of 6
  1. #1
    asmith78 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Aug 2011
    Posts
    15

    Syntax error in UPDATE statement HELP!

    Hello,

    I have created a SQL statement to update my Audit table within my database and keep receiving the Syntax error in UPDATE statement error message and cannot figure out why.

    Its in my strSQL2 statement but here is the majority of my coding:
    Please HELP!!! I've been working on it for almost 2.5 hrs now!

    I should add that I ran it in the immediate window and everything returns a value, the only thing I see odd is some of the premiums are "0" instead of "0.00" as those are currency fields. Not sure if this is the issue or how to correct. Here is the result from my immediate window:



    UPDATE tblAudit set tblAudit.audAuditorDateT1 = Date(), tblAudit.audTimeStampT1 = Now(), tblaudit.audGrpName = 'EQUITY TRANSPORTATION', tblaudit.audEmpSSN = '7527607', tblaudit.audEmpName = 'SOMERS, EVERETT', tblaudit.audGrpNumber = '5775', tblaudit.audACPrem = 5.61, tblaudit.audCIPrem = 0, tblaudit.audHIPrem = 0, tblaudit.audSTPrem = 0, tblaudit.audLTPrem = 5.61, tblaudit.audCAPrem = 0, tblaudit.audTLPrem = 0, tblaudit.audWLPrem = 0, tblaudit.audDNPrem = 0, tblaudit.audTotPrem = 5.61, tblaudit.audBillCode = '700', tblaudit.audCertificate = '5775000011', WHERE (((tblAudit.audAuditID)='DR000023'));

    Thanks!
    Amy
    Last edited by asmith78; 09-07-2011 at 02:42 PM. Reason: More info

  2. #2
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    strSQL2 = "UPDATE tblAudit set tblAudit.audAuditorDateT1 = Date(), tblAudit.audTimeStampT1 = Now(), " & _
    "tblaudit.audGrpName = '" & a & "'" & ", tblaudit.audEmpSSN = '" & b & "'" & ", tblaudit.audEmpName = '" & C & "'" & ", tblaudit.audGrpNumber = '" & D & "'" & ", " & _
    "tblaudit.audACPrem = " & E & ", tblaudit.audCIPrem = " & F & ", tblaudit.audHIPrem = " & G & ", tblaudit.audSTPrem = " & H & ", " & _
    "tblaudit.audLTPrem = " & J & ", tblaudit.audCAPrem = " & K & ", tblaudit.audTLPrem = " & L & ", tblaudit.audWLPrem = " & M & ", " & _
    "tblaudit.audDNPrem = " & N & ", tblaudit.audTotPrem = " & p & ", tblaudit.audBillCode = '" & q & "'" & ", tblaudit.audCertificate = '" & r & "'" & ", " & _
    " WHERE (((tblAudit.audAuditID)='" & AID & "'" & "));"

    This should be your SQL statement you had things like

    tblaudit.audTotPrem = " & p & "" & ",

    What this actually converts to when it tries to insert is

    <whatever p is>"

    so if your value of p is 5 it's trying to insert 5" ( a text string) into what I assume is a numeric field.

    If you are trying to create an inch denotation in your data, don't. Just assume your data is in inches. or if it's possible for you to have feet/yards/miles in your data convert it all to inches before you append it.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Based on the Immediate window results, the problem that jumps out is the comma before WHERE, which would cause a syntax error.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    ooh good catch baldy

  5. #5
    asmith78 is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Aug 2011
    Posts
    15
    Thank you both so much! PBaldy, that was it, even though I SWEAR I removed that dang comma like three times!!! HAHAH! Got to love Access!

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Happy to help. It's always the little things that trip us up.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 7
    Last Post: 08-17-2011, 01:49 PM
  2. UPDATE Statement Correct Syntax
    By OMGsh Y did I say Yes in forum Forms
    Replies: 2
    Last Post: 02-23-2011, 01:42 PM
  3. IIf Statement Syntax Error
    By shexe in forum Queries
    Replies: 2
    Last Post: 10-06-2010, 08:35 AM
  4. Replies: 3
    Last Post: 07-21-2010, 02:41 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