Explain SQL UPDATE statement punctuation (1 Viewer)

Single & Double quotes?

I had been having trouble finding VB code to update records in a database. I found many different examples but the following is the only one I found that actually worked for me.


Dim updateRec as String


updateRec = "UPDATE dbo_HB_ITEMMASTER SET dbo_HB_ITEMMASTER.Total_AuthQty = '" & Me.txtNewAuthTotal & "', dbo_HB_ITEMMASTER.Auth_Bal = '" & Me.txtNewAuthBal & "' WHERE dbo_HB_ITEMMASTER.PartNum = '" & Me.cboHBItem & "'"


CurrentProject.Connection.Execute updateRec


I understand the SQL statement but can someone explain the punctuation? The difference between the double quote and single quote? I’ve seen example code which just uses the double quotes but that never seems to work for me.


I am using this code but I’d like to completely understand what it’s doing, or what the punctuation is doing, especially the single quotes.

Any String in SQL is accessed by using a Single quote (').. Say if it Name, Address..

* So when you are trying to use the values it should be enclosed in single qoutes as 'Paul', 'Jen', 'BS16 4DH'..

* So when you use numbers as values this should not be enclosed in any quotes 43,10245,11.01.

* Tricky now.. when you enclose variables in the update statements, they should be enclosed inside the double quotes followed by &. So if you have a variable varName=Paul, when accessing you will normally use " & varName & ".. because of the fact that Paul is a string you have to enclose inside single quotes.. Thus it seems somewhat messy like this..
'" & varName & "'..

Thus your final update string looks like..

strSQL = "UPDATE Customers SET FirstName='" & varName & "' WHERE ID=" & cIDnum & " "

Hope this makes sense. !

aah, I see. You included the single quote around varName because it's a string but only double around cIDnum because it's not a string, probably an Int.

Yes, that makes sense now and explains why other snippets of code never worked for me.
Could I bother you with one more question? What is the punctuation used to truncate to the next line so I can see the whole statement on the screen? I've seen the underscore used but I must be missing something with that too since I've not been able to get that to work either.

Thanks so much for taking the time to explain!

Yes you got that correct. For numbers you do not need to use single quotes. Well yes sometimes it is quiet tricky using the _ to break.. Well.. use a space between the _ and the other components, you should also be concerned about quotes.. the following will work..

MsgBox( _
"Hello" _
)

The following will not, because you are breaking a string...

MsgBox( _
"Hello _
Paul" _
)

Because you are breaking the string without any Quotes.. so when you do something like that make sure the strings are properly closed and concatenated..

MsgBox( _
"Hello" _
& "Paul" _
)

BTW.. You are welcome.. Glad to help.

Thanks again!! That worked perfectly! Thanks again for all your help!

You are welcome..