Results 1 to 5 of 5
  1. #1
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20

    searching for names with apostrophes

    I have a form with a subform.

    The form has a combo box, and combo box pulls from a table of patient names, specifically the last name. I use this form to either type the last name of the patient or (obviously) use the pull down feature to look for the last name in the list.

    I have this code that will reveal in the subform all the patients with the last name.

    Private Sub cboNameSelector_AfterUpdate()
    Dim myName As String


    myName = "Select * from tbl_PatientsRecord where ([LastName] = '" & Me.cboNameSelector & "')"
    Me.tbl_PatientsRecord_subform1.Form.RecordSource = myName
    Me.tbl_PatientsRecord_subform1.Form.Requery
    End Sub
    Works beautifully for any name that doesn't have an apostrophe. If there is a last name with an apostrophe (O'Henry - names like that), I get a 3075 run time error -- I suspect because the name has the apostrophe.

    How can I get around this?

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Some thoughts and methods here:

    http://www.baldyweb.com/BuildSQL.htm
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    dont use sql, use a query, it allows it.
    or
    use chr(34) instead of the single quote.
    or
    use a constant set to dbl-quote

    const Q = """"
    myName = "Select * from tbl_PatientsRecord where ([LastName] = " & Q & Me.cboNameSelector & Q & ")"

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    See the discussion in this link regarding escaping a single quote

  5. #5
    kwelch is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2009
    Location
    Chicago, IL, USA
    Posts
    20
    Thank you for the links.

    I went with the const statement; it works very nicely. Thanks for the suggestions.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 11
    Last Post: 10-30-2016, 05:39 AM
  2. Replies: 6
    Last Post: 06-02-2016, 08:07 PM
  3. sql variable with apostrophes
    By broecher in forum Programming
    Replies: 3
    Last Post: 04-23-2013, 11:00 AM
  4. Replies: 2
    Last Post: 04-17-2013, 04:59 AM
  5. Replies: 4
    Last Post: 09-05-2011, 11:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums