Quotes within strings (A2000)

Topic

New Reply

I’m having a bit of a problem working out how to deal with the concatenation within a query definition in a procedure. Below is the code which is causing problems (I’ve edited out most of the fields being selected):

strSQL = “SELECT [OwnerTitle] & ” ” & [OwnerFirstName] & ” ” & [OwnerLastName] AS OwnerName, ” & _
“tblOwners.OwnerID FROM tblOwners WHERE tblOwners.OwnerID = ” & intAddressID & “;”

This fails because of the 2 ‘& ” ” &’ within the first line and I can’t figure out how to prevent it. If I remove these and just concatenate the fields the proc runs OK, but of course doesn’t show the name correctly.

Reply | Quote

Replies

If you want to include quotes within a quoted string, you must either use single quotes or double double quotes, so:

"SELECT [OwnerTitle] & ' ' & [OwnerFirstName] & ' ' & [OwnerLastName] AS OwnerName, "

or

"SELECT [OwnerTitle] & "" "" & [OwnerFirstName] & "" "" & [OwnerLastName] AS OwnerName, "

In this example, another possibility would be to use Chr(32) or Space(1) for a space:

"... [OwnerTitle] & Chr(32) & [OwnerFirstName] & Chr(32) & [OwnerLastName] AS ..."

Reply | Quote

Thanks Hans. The use of Chr32 seems a very elegant way of adding spaces without having to be bothered by which quotes go where.

Reply | Quote

Thanks Hans. The use of Chr32 seems a very elegant way of adding spaces without having to be bothered by which quotes go where.

Reply | Quote

If you want to include quotes within a quoted string, you must either use single quotes or double double quotes, so:

"SELECT [OwnerTitle] & ' ' & [OwnerFirstName] & ' ' & [OwnerLastName] AS OwnerName, "

or

"SELECT [OwnerTitle] & "" "" & [OwnerFirstName] & "" "" & [OwnerLastName] AS OwnerName, "

In this example, another possibility would be to use Chr(32) or Space(1) for a space:

"... [OwnerTitle] & Chr(32) & [OwnerFirstName] & Chr(32) & [OwnerLastName] AS ..."

Reply | Quote