syntax error in SQL Update statement

Question

I'm getting a syntax error in my UPDATE statement, but I'm not sure where exactly it is. Here's my code:

    strSelected = "UPDATE CFRRR SET assignedby = " & Me.cmbassignedby.Column(1) & ", assignedto = " & _
                                  Me.cmbassignedto.Column(2) & ", Dateassigned = " & Now() & ", actiondate = " & _
                                  Now() & ", Workername = " & Me.cmbassignedto.Column(2) & ", WorkerID = " & _
                                  Me.cmbassignedto.Column(1) & " WHERE CFRRRID In ( " & strSelected & " );"
CurrentDb.Execute strSelected

Peter Schneider · Accepted Answer · 2015-05-02 20:39:18Z

1

It's most likely because of the Now() function, which also prints the current time (seperated with a space) - hence the syntax error. Try to surround them with single quotation marks.

You can also print out the SQL Statement Debug.Print strSelected to see what you have concatenated...

answered May 2, 2015 at 20:39

Peter Schneider

2,9591 gold badge16 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Peter Schneider Over a year ago

What if you surround the date values with # ? Something like ... Dateassigned = #" & Now() & "#, action..."

Peter Schneider Over a year ago

So the string you are trying to construct has to be a valid sql query. This means text values should be surounded by single quotation marks, the date values should be surounded by #. Did you check if all variables contain values? The best way to find out is to Debug.Print the strSelected variable to see the resulting query. Use Ctrl + G to show the Immediate Window inside the VBA Editor.

Lilly Over a year ago

I'm not getting anything when I put Debug.Print strSelected in the immediate window.

Peter Schneider Over a year ago

You have to insert the Debug.Print statement right before CurrentDb.Execute strSelected and set a breakpoint on CurrentDb.Execute. When you execute your code switch to the Immediate Window and examine the resulting string...

Lilly Over a year ago

So theses are the results of the code in the immediate window. I don't see a problem though:

UPDATE CFRRR SET CFRRR.assignedby = Smith, Jane, CFRRR.assignedto = Smith, John, CFRRR.Dateassigned = 5/3/2015 11:26:32 AM, CFRRR.actiondate = 5/3/2015 11:26:32 AM, CFRRR.Workername = Smith, John, CFRRR.WorkerID = 6 WHERE CFRRRID In ( 84 );

|

Collectives™ on Stack Overflow

syntax error in SQL Update statement

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related