Issue with line continuation when running SQL in VBA (1 Viewer)

[JDubya]

Hi All, If a run the following SQL string from within VBA and the string is all on one line, it works fine:-

SQL = "UPDATE tblContactImport INNER JOIN Customers ON tblContactImport.cuco_Company = Customers.cu_ID SET tblContactImport.cuco_NewPhone = Mid(Customers.cu_PhoneNumber,1,5)+' '+Mid(tblContactImport.cuco_NewPhone,1,3)+' '+Mid(tblContactImport.cuco_NewPhone,4,3);"

However, when I break it up onto several lines using line continuation (see below) so that the code better fits the window I get a syntax error, missing operator message.

SQL = "UPDATE tblContactImport INNER JOIN Customers ON tblContactImport.cuco_Company = Customers.cu_ID" & _
"SET tblContactImport.cuco_NewPhone =" & _
"Mid(Customers.cu_PhoneNumber,1,5)+' '+Mid(tblContactImport.cuco_NewPhone,1,3)+' '+Mid(tblContactImport.cuco_NewPhone,4,3);"

I've also tried puting the & on the next line but still but the same error.

SQL = "UPDATE tblContactImport INNER JOIN Customers ON tblContactImport.cuco_Company = Customers.cu_ID" _
& "SET tblContactImport.cuco_NewPhone =" _
& "Mid(Customers.cu_PhoneNumber,1,5)+' '+Mid(tblContactImport.cuco_NewPhone,1,3)+' '+Mid(tblContactImport.cuco_NewPhone,4,3);"



Can anybody advise on what i'm doing wrong?
Cheers, John

 

[Ranman256]

Either use the underscores OR the ampersands, not both.....

SQL = "UPDATE tblContactImport INNER JOIN Customers ON Sql = tblContactImport.cuco_Company = Customers.cu_ID"
SQL = Sql & " SET tblContactImport.cuco_NewPhone ="
SQL = Sql & " Mid(Customers.cu_PhoneNumber,1,5) & ' ' & Mid(tblContactImport.cuco_NewPhone,1,3) & ' ' & Mid tblContactImport.cuco_NewPhone,4,3);"

 

[CJ_London]

you are missing spaces - this

SQL = "UPDATE tblContactImport INNER JOIN Customers ON tblContactImport.cuco_Company = Customers.cu_ID" & _
"SET tblContactImport.cuco_NewPhone =" &

needs to be

"UPDATE tblContactImport INNER JOIN Customers ON tblContactImport.cuco_Company = Customers.cu_ID" & _
" SET tblContactImport.cuco_NewPhone =" &

 

[Minty]

Always put a debug.print Sql in after you have created a SQL string, you can then see exactly what you are trying to pass as a query string. Once it is working simply comment it out.
http://www.baldyweb.com/immediatewindow.htm

 

[JDubya]

Thanks Ranman256, I guess it is true that there's more than one way to skin a cat. For the record I would just like to confirm that I have never ever skinned a cat, do not ever intend skinning cats, or condone the skinning of cats.

 

[JDubya]

Hi CJ, thanks for the catch, it works perfectly! Cheers, John

 

[JDubya]

Hi Minty, that's a really useful tip and made it simple for me to see where i went wrong. I'd give you an official thanks for your post if I could but for some reason I don't have the option....I don't know if it's because I've already thanked CJ?

 

[Minty]

I'm not sure tbh, the reputation/thanks thing is a bit odd on here, not that it bothers me. A thank you post is more than sufficient.

 

[JDubya]

Thanks for your help Minty