Is my query and use of apostrophes right?

Question

Trying to get this query right where it takes in variables to update the records. I tried playing around with the quotation marks and either I would get "Enter a parameter value" dialogue screen or "Syntax error (Missing Operator)" error.

updateOriginalBudget_SQL = "UPDATE SubPhases SET OriginalBudget=" & matchBudget & " " & "WHERE ShopCode=" & elemShopCode & ";"
DoCmd.RunSQL updateOriginalBudget_SQL

matchBudget is an integer and elemShopCode is a String. If I just write a simple query like:

"UPDATE SubPhases SET OriginalBudget=5 WHERE ShopCode='344S-23';"

it'll work with no issues. Any idea what I am doing wrong here?

If you use parameters, you won't get this issue, and you avoid the risk of SQL injection. See [this post](https://stackoverflow.com/q/49509615/7296893) for how to do that. — Erik A, Oct 14 '19 at 10:29

Louis · Accepted Answer · 2019-10-14T10:35:01.507

1

In the original query, you're missing the quotes '' for field values. In your ShopCode you have a value with - which is an operator and that's why you're getting a "Syntax error (Missing Operator)" error.

Change the query like this and it will work:

updateOriginalBudget_SQL = "UPDATE SubPhases SET OriginalBudget=" & matchBudget & " WHERE ShopCode='" & elemShopCode & "';"

I hope this helps.

edited Oct 14 '19 at 10:35

answered Oct 14 '19 at 10:24

Louis

3,592
2
10
18

You had quoted the number. – Gustav Oct 14 '19 at 10:26
@Louis Oh I know now what I was doing wrong. This solved my issue. – AutoMate Oct 14 '19 at 17:32

Is my query and use of apostrophes right?

1 Answers1