jiangzhou He jiangzhou He - 1 month ago 16
SQL Question

Syntax error in SQL update

I am new to both MS Access and SQL. Now I am trying to create an inventory database for our company in Ms Access. I try to extract data from the reception form to update the inventory balance. But I met a syntax error message when I executed a SQL

update
statement. This is weird for me because I used the same statements that successfully running in other tables. The only difference is my former successful update working by direct text replacement and my error occurring update is working in a numeric object.

Please help me to check where I am wrong.

This is my code:

Private Sub Command96_Click()
CurrentDb.Execute "UPDATE tbl_Current_Stock" & _
"SET Stock_Level= Stock_Level + " & Me!txtOrderQty & "" & _
"Where tbl_Current_Stock.Raw_Material= " & Me!cboPurchase.Column(1) & ""
End Sub


Thanks!

Answer

Friend, follow some tips to generate your updade correctly:

  • Check the spaces after concatenating your query
  • Be careful not to generate queries with keywords stuck together

UPDATE tableTestSET nome = 'My Name' WHERE active IS NOT NULL (wrong)

UPDATE tableTest SET name = 'My Name' WHERE active IS NOT NULL 
  • Do not forget to use quotation marks when using strings

UPDATE tableTest SET name = My Name WHERE active IS NOT NULL (wrong)

UPDATE tableTest SET name = 'My Name' WHERE active IS NOT NULL

I hope it helps...

Good Luck!

Comments