I've tried to wrap my head around the logic of how quotes work, but I cannot find any official documentation from Microsoft that really explains this in depth. I think the logic behind this code works but of course it doesn't. Here's my logic: The double quotes in front of UPDATE start the string and the double quotes behind Sector ends the string. Variables and strings must be separated by an ampersand. Done. Now I need to continue the string so I wrap WHERE ID to concatenate it to the variable, and rst!ID is not a string so it needs no quotes. STILL WRONG. Please help me understand where my logic is off here? Note: test is a string variable.

Thank you!

DoCmd.RunSQL "UPDATE MyTable SET MyField = 'Sector'" & test & "WHERE ID =" rst!ID

CodePudding user response：

that should be

DoCmd.RunSQL "UPDATE MyTable SET MyField = 'Sector" & test & "' WHERE ID =" rst!ID

First the ' needs to go behind "test", second there is a space necessary before "where".

CodePudding user response：

1. Your whole String value needs to be into single quote like this: ...." MyField = 'Sector" & test & "' ...."
2. You should also be careful about spaces before and after double quotes because it will give you syntax errors in your sql statement.
3. When you call your table in vba access maybe you need to put your table name into brackets (at least in my code this was a problem)

So, I would write it like this:

DoCmd.RunSQL "UPDATE [MyTable] a  SET [MyField] = 'Sector" & test & "' WHERE a.[ID] ="& rst!ID

You could also print the command before execution just to see what is the problem, sometimes it helps a lot. So I would recommend you to run this at first, without then RunSQL command:

MsgBox "UPDATE [MyTable] a  SET [MyField] = 'Sector" & test & "' WHERE a.[ID] ="& rst!ID

and make changes of the wrong parts.