Results 1 to 8 of 8
  1. #1
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40

    Syntax Error in UPDATE Query


    Hi All,
    Can someone see what is wrong with my update query? It looks good to me but obviously something is wrong.
    Code:
    "UPDATE CGBoxStatusIn SET CGBoxStatusIn.ScanOut.Value =(#2/19/2018 11:17:03 AM#), SET CGBoxStatusIn.missingOut.Value =(3), WHERE (((CGBoxStatusIn.CGBox.Value) ='46001-1'));"
    Thanks,
    Lenny

  2. #2
    Join Date
    Apr 2017
    Posts
    1,673
    Code:
    UPDATE CGBoxStatusIn SET CGBoxStatusIn.ScanOut =(#2/19/2018 11:17:03 AM#), CGBoxStatusIn.missingOut =(3) WHERE (((CGBoxStatusIn.CGBox) ='46001-1'));

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Lenny,

    If your SQL attempt looked good to you, then I recommend you review SQL syntax and a few tutorials.

    Good luck and don't overlook Google as a valuable research tool.

  4. #4
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Still get an error: So I put the actual code on here:
    Code:
    str_SQL = "UPDATE CGBoxStatusIn "            
                str_SQL = str_SQL & "SET CGBoxStatusIn.ScanOut=(#" & str_ScanIn & "#), "
                str_SQL = str_SQL & "SET CGBoxStatusIn.missingOut=(" & Missingvalue & "), "
                str_SQL = str_SQL & "WHERE (((CGBoxStatusIn.CGBox) ='" & str_CGBox & "'));"

    Building a query......Stepping through the code it puts all the right info into the variables however I get an error



    Thanks in advance,
    Lenny

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    An UPDATE query has only 1 SET -- look at the link I gave you or use Google to find examples.

  6. #6
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40
    Ok, I took out the one SET word and still get an error, here's the query:
    Code:
                str_SQL = "UPDATE CGBoxStatusIn "            
                str_SQL = str_SQL & "SET CGBoxStatusIn.ScanOut=(#" & str_ScanIn & "#), "
                str_SQL = str_SQL & "CGBoxStatusIn.missingOut=(" & Missingvalue & "), "
                str_SQL = str_SQL & "WHERE (((CGBoxStatusIn.CGBox) ='" & str_CGBox & "'));"
                
                CurrentDb.Execute str_SQL, dbFailOnError

    Thanks

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Comment out the
    CurrentDb.Execute str_SQL, dbFailOnError

    and add a line
    Debug.print strSQL

    run the routine then post what the debug.print shows

  8. #8
    lamore48 is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    40

    Found It

    There was an extra comma, in the statement.

    Thanks for your help

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

Similar Threads

  1. Syntax error in UPDATE query
    By GraeagleBill in forum Programming
    Replies: 4
    Last Post: 02-14-2018, 12:38 PM
  2. Replies: 2
    Last Post: 10-09-2014, 10:53 AM
  3. Replies: 1
    Last Post: 09-22-2014, 08:54 AM
  4. Error 3144: Syntax error in UPDATE statement??
    By Paintballlovr in forum Programming
    Replies: 7
    Last Post: 03-26-2014, 12:53 PM
  5. Syntax Error 3144 in SQL Update Query.
    By Phred in forum Programming
    Replies: 4
    Last Post: 03-02-2012, 02:39 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