Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quotes within strings (A2000)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Quotes within strings (A2000)

    If you want to include quotes within a quoted string, you must either use single quotes or double double quotes, so:<pre>"SELECT [OwnerTitle] & ' ' & [OwnerFirstName] & ' ' & [OwnerLastName] AS OwnerName, "</pre>

    or<pre>"SELECT [OwnerTitle] & "" "" & [OwnerFirstName] & "" "" & [OwnerLastName] AS OwnerName, "</pre>

    In this example, another possibility would be to use Chr(32) or Space(1) for a space:<pre>"... [OwnerTitle] & Chr(32) & [OwnerFirstName] & Chr(32) & [OwnerLastName] AS ..."</pre>


  3. #3
    3 Star Lounger
    Join Date
    Jun 2002
    Location
    Nottingham, Nottinghamshire, United Kingdom
    Posts
    257
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes within strings (A2000)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •