Syntax error for SQL update statement in access

Question

I ran a SQL UPDATE statement in MS Access, but received a error message

Syntax error (missing operator) in query expression

I searched in internet, but any solutions works in my situation.

My code is:

Private Sub Command111_Click()
     CurrentDb.Execute " UPDATE test3 " & _
                       "SET OrderStatus= 'Producing' " & _
                       "WHERE OrderID='" & Me!cboOrderID1 & "' ProductName='" & Me!ComboProduct1 & "'"
End Sub

Please help me check where is wrong.

Thanks

Pretty sure there should be an AND between the two conditions you have there. — Niet the Dark Absol
– Niet the Dark Absol, Commented Jun 30, 2016 at 13:58

Hynek Bernard · Accepted Answer · 2016-06-30 13:59:21Z

1

I am assuming that OrderID is int so it cannot be in '' quotes and must be as number in statement

"WHERE OrderID=" & Me!cboOrderID1 & " AND/OR ProductName='" & Me!ComboProduct1 & "'"

Also you forgot to use AND or OR

answered Jun 30, 2016 at 13:59

Hynek Bernard

7461 gold badge12 silver badges32 bronze badges

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

Comments

Justin Woodmancy · Accepted Answer · 2016-06-30 14:02:36Z

1

Forgot your AND

 Private Sub Command111_Click()

    CurrentDb.Execute " UPDATE test3 " & _
    "SET OrderStatus= 'Producing' " & _
    "WHERE OrderID='" & Me!cboOrderID1 & "' AND ProductName='" & Me!ComboProduct1 & "'"
    End Sub

answered Jun 30, 2016 at 14:02

Justin Woodmancy

9626 silver badges11 bronze badges

2 Comments

jiangzhou He Over a year ago

thank you very much Justin for your quick response. the code now looks good but I received a mistype error. what I did is to update OrderStatus in a table based on a form input. In the table, OrderID is a string, ProductName is numeric looking up reference, OrderStatus is a string. In the form, OrderID is combo box sourced from the table, ProductName in the form is a combo box originated from the same source as the ProductName in table. if I do an append , those data seems good without any mistype error. could you kindly help me analyze where is the mistype source.

Justin Woodmancy Over a year ago

did you try cboOrderID1.Text, and ComboProduct1.Value? Are you sure that OrderID is a string? It would make sense to me that OrderID would be a numeric value, and ProductName would be the string. If that is the case you would want your WHERE to be: Where OrderID='" & cboOrderID1.value & "' AND ProductName='" & ComboProduct1.Text & "'"

jiangzhou He · Accepted Answer · 2016-07-04 18:14:15Z

1

Thank you guys! This code works in my situation.

Private Sub Command111_Click()

CurrentDb.Execute " UPDATE test3 " & _
"SET OrderStatus= 'Producing' " & _
"WHERE OrderID='" & Me!cboOrderID1 & "' AND ProductName=" &CStr(Me!ComboProduct1) & ""
End Sub

In my case, the ProductName is sourced from a combo box.

answered Jul 4, 2016 at 18:14

jiangzhou He

571 silver badge7 bronze badges

Collectives™ on Stack Overflow

Syntax error for SQL update statement in access

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related