CODEWITHSUNDEEP
sqlms-access
jiangzhou He
jiangzhou He

Reputation: 57

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!

Upvotes: 0

Views: 76

Answers (3)

ℛɑƒæĿᴿᴹᴿ
ℛɑƒæĿᴿᴹᴿ

Reputation: 5366

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!

Upvotes: 1

maulik kansara
maulik kansara

Reputation: 1107

check your sentence correctly. there is no technical error. there are some space missing in you query.

just add white space before "SET" and "where" words.

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

Upvotes: 2

Robert Columbia
Robert Columbia

Reputation: 6418

You need to add spaces before SET and Where. Otherwise, your command will look something like UPDATE tbl_Current_stockSET Stock_Level= Stock_Level + 3Where.....

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

You might also need to wrap the Raw_Material column in quotes if it is not numeric.

Upvotes: 3

Related Questions