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
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