Results 1 to 4 of 4
  1. #1
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Quotes in VBA!!! (A97/SR2)

    I have the below piece of code in a sub module on an onclick event. However, it uses data gained from a table that has a record in it with a single quote i.e. "Phil's" (held in rec.Fields("establishmentname")). As VBA/SQL uses single quotes to identify text, the one in the field is picked up and the module crashes. Is there any parenthesis that can be put around the name so the single quote is ignored?

    Set CountED = CurrentDb().OpenRecordset("Select count(EstablishmentName) as countofEN " & _
    "FROM [tblShortlistMailmerge] " & _
    "WHERE Establishmentname <'" & rec.Fields("establishmentname") & "'")

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes in VBA!!! (A97/SR2)

    You can always use chr(34)
    <pre>Set CountED = CurrentDb().OpenRecordset("Select count(EstablishmentName) as countofEN " & _
    "FROM [tblShortlistMailmerge] " & _
    "WHERE Establishmentname <" & chr(34) & rec.Fields("establishmentname") & chr(34))
    </pre>

    Francois

  3. #3
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Gillingham, Kent, England
    Posts
    511
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes in VBA!!! (A97/SR2)

    Excellent, that worked like a treat thanks Francois.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Quotes in VBA!!! (A97/SR2)

    Thanks!

Posting Permissions

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