Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apostrophies (Access 97 SR2)

    I have a DB where when the user tries to do a lookup for a client name, if the client name has an apostrophe in it (i.e. At. Ann's) it doesn't show up. Why? This happens on several of my DB's.

    Thanks
    Nadia

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Apostrophies (Access 97 SR2)

    How are you doing the lookup? Are you using a combo box, looking up data in a table, per a list created in a combo box? Please provide a little more data. Thanks.
    Regards,

    Gary
    (It's been a while!)

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

    Re: Apostrophies (Access 97 SR2)

    Let me guess. You have code like this:
    <pre>" ... ClientName = '" & Me.txtClientName & "'"</pre>

    The value from txtClientName is enclosed in single quotes (apostrophies): 'Nadia'. When the name contains an apostrophy, Access thinks the name ends there: 'At Ann's' and chokes on what comes after it.

    One solution is to use double quotes. Because they should be part of the string, you must double them:
    <pre>" ... ClientName = """ & Me.txtClientName & """"</pre>

    Looks horrible, doesn't it? But it works. You can also use Chr(34):
    <pre>" ... ClientName = " & Chr(34) & Me.txtClientName & Chr(34)</pre>

    If there is a chance that your client names contain double quotes too, you must double them. You can use a custom function for that:

    Function FixQuotes(aText As String) As String
    Dim i As Integer
    FixQuotes = aText
    i = InStr(FixQuotes, """")
    Do While i > 0
    FixQuotes = Left$(FixQuotes, i) & """" & Mid$(FixQuotes, i + 1)
    i = InStr(i + 2, FixQuotes, """")
    Loop
    End Function

    Then you can use
    <pre>" ... ClientName = " & Chr(34) & FixQuotes(Me.txtClientName) & Chr(34)</pre>


  4. #4
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophies (Access 97 SR2)

    I'm using a combo box and below is the code:

    Sub Combo20_AfterUpdate()
    Me.RecordsetClone.FindFirst "[ClientName] = '" & Me![Combo20] & "'"
    Me.Bookmark = Me.RecordsetClone.Bookmark
    End Sub

  5. #5
    Lounger
    Join Date
    Oct 2001
    Location
    New York
    Posts
    34
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophies (Access 97 SR2)

    Thank you! the solution to use double quotes worked!

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

    Re: Apostrophies (Access 97 SR2)

    If you replace Me.txtClientName with Me1[Combo20] in my <!post=previous reply,153111>previous reply<!/post>, I think you'll be OK.

    Added: sorry, you already replied.

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    Kent, Kent, United Kingdom
    Posts
    52
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophies (Access 97 SR2)

    You might want to try the using LIKE rather than Equals to

    (It's not a pretty solution but I thought I'd suggest it for completness)

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Apostrophies (Access 97 SR2)

    I knew about using Double quotes if the single quotes were already in the code, however I didn't know about the double Double Quotes. Good one Hans.

    PS. I won't ask what happens if someone has used the double Double Quotes in their data, and how you would go about solving that one. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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