format SQL string to use in procedure

  • Thread starter Thread starter tim johnson
  • Start date Start date
T

tim johnson

How can I format this SQL string taken form the SQL view
of a query so I van use it in a procedure.

I am having problems with the double quotes.

SELECT tblCustomer.CustomerID, [LastName] & ", " &
[FirstName] & " " & [MI] AS Customer, tblCustomer.Street,
tblCustomer.City, tblCustomer.State, tblCustomer.Zip
FROM tblCustomer
ORDER BY [LastName] & ", " & [FirstName] & " " & [MI];


Thanks
 
Replace each quote(") with two quotes("").

strSQL = "SELECT tblCustomer.CustomerID, [LastName] & "", "" & _
" [FirstName] & "" "" & [MI] AS Customer, tblCustomer.Street, "
& _
" tblCustomer.City, tblCustomer.State, tblCustomer.Zip " & _
" FROM tblCustomer " & _
"ORDER BY [LastName] & "", "" & [FirstName] & "" "" & [MI];"
debug.Print strSQL

I always add the debug.print line so I can see how it is being evaluated. I
can copy the string from the immediate window and paste it into a new blank
query and test it.
 
Hi Tim,

Give the following a try:

strSQL = "SELECT tblCustomer.CustomerID, [LastName] & " & _
Chr(34) & ", " & Chr(34) & _
" & [FirstName] & Space$(1) & [MI] AS Customer, " & _
"tblCustomer.Street, tblCustomer.City , tblCustomer.State, " & _
"tblCustomer.Zip FROM tblCustomer " & _
"ORDER BY [LastName], [FirstName], [MI];"


hth,
 
Back
Top