Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Victoria BC, Canada
    Posts
    6

    Unanswered: Apostrophes in name field breaks string

    Hi there.
    In my Personnel database, some folks who have recently joined the company have apostrophes in their last names. This causes all manner of problems, such as when I am trying to filter records on their last name, or make statements such as Name:[namefirst] & " " & [namelast], because as soon as the code hits the apostrophe in the namelast field, it breaks the string.

    Any ideas?

    I'm hoping your idea doesn't involve me rebuilding allllllll the code that references the [namelast] field. Also, I've discovered that people are pretty insistent about keeping their apostrophes (ie: LArcheveque is not acceptable as a replacement for L'Archeveque).

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Apostrophes in name field breaks string

    Originally posted by geniebean
    Hi there.
    In my Personnel database, some folks who have recently joined the company have apostrophes in their last names. This causes all manner of problems, such as when I am trying to filter records on their last name, or make statements such as Name:[namefirst] & " " & [namelast], because as soon as the code hits the apostrophe in the namelast field, it breaks the string.

    Any ideas?

    I'm hoping your idea doesn't involve me rebuilding allllllll the code that references the [namelast] field. Also, I've discovered that people are pretty insistent about keeping their apostrophes (ie: LArcheveque is not acceptable as a replacement for L'Archeveque).
    Try these functions in a standard module to control the quote situation. This comes from the Access 97 Developers handbook so I can't take credit. It does work though.

    Function adhHandleQuotes(ByVal varValue As Variant, ByVal strDelimiter As String) As Variant

    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, and Gilbert (Sybex)
    ' Copyright 1997. All rights reserved.

    ' Replace all instances of strdelimiter with varValue with TWO instances,
    ' thereby handling the darned quote issue once and for all.

    ' Returns Null if varValue was Null, otherwise
    ' returns varValue with all instances of strDelimiter
    ' replaced with two of each.
    ' adhHandleQuotes("This 'is' a test", "'") returns
    ' "This ''is'' a test"

    adhHandleQuotes = adhReplace(varValue, strDelimiter, strDelimiter & strDelimiter)
    End Function

    Function adhReplace(ByVal varValue As Variant, ByVal strFind As String, ByVal strReplace As String) As Variant

    ' From Access 97 Developer's Handbook
    ' by Litwin, Getz, Gilbert (Sybex)
    ' Copyright 1997. All rights reserved.

    ' Replace all instances of strFind with strReplace in varValue.

    Dim intLenFind As Integer
    Dim intLenReplace As Integer
    Dim intPos As Integer

    If IsNull(varValue) Then
    adhReplace = Null
    Else
    intLenFind = Len(strFind)
    intLenReplace = Len(strReplace)

    intPos = 1
    Do
    intPos = InStr(intPos, varValue, strFind)
    If intPos > 0 Then
    varValue = Left(varValue, intPos - 1) & strReplace & Mid(varValue, intPos + intLenFind)
    intPos = intPos + intLenReplace
    End If
    Loop Until intPos = 0
    End If
    adhReplace = varValue
    End Function
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

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