Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Apostrophes in text fields (2003 (11.6566.6568) SP2)

    Hi there!

    Been away for a while, but now back to plague all and sundry.

    I have recently converted a 97 db to 2003 for my local Gym club and having completed this, a number of additional requests have come my way for improvement etc.

    One of these is entering descriptions for various monetary adjustments that they have to make. When entering names such as O'Malley or O'Brien the dear old apostrophe causes an error with the whole sql text string to the point that the process throws up a run time error of 3075 and asks the user to end or debug and thus the adjustment fails.

    Other than telling them not to use apostrophes in their descriptions, Is there anyway I can get the sql to accept the apostrophe and enable them to carry out adjustments in a normal manner?

    Cheers,

    Niven

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

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    You're probably using single quotes ' around text values; you can concatenate with Chr(34) instead, as explained in Chr() function and quotes within strings (all). Post back if that doesn't help.

  3. #3
    3 Star Lounger
    Join Date
    Nov 2003
    Location
    London, Gtr London, England
    Posts
    222
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    Hans,

    Top stuff! I've been racking my brains over this. I didn't think it could be done that simply. Also when searching the forum, I didn't think to use "single quotes" as well as "apostrophe".

    Cheers, I'm off to drink some blonde leffe now!

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

  4. #4
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    Run the fields that potentially have an appostrophe through this procedure. This procedure was set up for Chr(34) and I don't remember if that's the single or double quote but it is easly changed to fit your needs. I built the procedure because I work with database products that have different characters that cause the same problem. For example, I believe it's the double-quote that messes with Access/Jet but the single quote that causes problems with SQL Server and other server based database systems.

    Public Function AddSpecialQuotes(ByVal strSource As Variant) As Variant
    'Written by Patrick O. Headley, Linx Consulting, Inc., 3/20/03.
    'Parses a string for a single double-quote character. If any are found
    'they are turned into pairs of double-quotes so the string can be used
    'as a value in a database field or as a criteria expression, in code.

    10 On Error GoTo AddSpecialQuotesError

    Variables:
    Dim lngQuotePosition As String
    Dim strSourceCharacter As String

    Start:
    'See if there is text to process.
    20 If Not IsNull(strSource) Then

    'See if there are even any quotes in the string.
    30 If InStr(strSource, Chr$(34)) > 0 Then

    'Parse the source string.
    40 Do Until Len(strSource) = 0

    'See if the left most character in the source string is a quote.
    50 If Left(strSource, 1) = Chr$(34) Then

    60 AddSpecialQuotes = AddSpecialQuotes & Chr$(34) & Chr$(34)
    70 Else

    'The character is a printable character so copy it straight across to the target string.
    80 AddSpecialQuotes = AddSpecialQuotes & Left(strSource, 1)
    90 End If

    'Remove the left most character from the source string.
    100 strSource = Right(strSource, Len(strSource) - 1)
    110 Loop 'Check the next character
    120 Else

    'There are no quotes in the source string so copy the entire string to the target.
    130 AddSpecialQuotes = strSource
    140 End If 'For seeing if there are any quotes in the string.
    150 Else

    'The source value is null so return a null.
    160 AddSpecialQuotes = strSource
    170 End If

    Done:
    180 Exit Function

    AddSpecialQuotesError:
    190 AddSpecialQuotes = ""
    200 ErrorHandler "PublicFunctions", "AddSpecialQuotesError:", Erl
    210 Resume Done

    End Function


    Good luck.

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

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    1) Chr(34) is the double quote.
    2) I wouldn't use the function to update records in a table - the double double quotes look weird on screen.
    3) This function won't work for others since you didn't include the error handling function.
    4) Why don't you use the Replace function? It's much faster and more efficient than looping through the characters of the string:

    Public Function AddSpecialQuotes(ByVal strSource As Variant) As Variant
    On Error GoTo AddSpecialQuotesError
    ' Default return value
    AddSpecialQuotes = Null
    If Not IsNull(strSource) Then
    AddSpecialQuotes = Replace(strSource, Chr(34), Chr(34) & Chr(34))
    End If
    Exit Function

    AddSpecialQuotesError:
    MsgBox Err.Description, vbExclamation
    End Function

  6. #6
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    Good point on the error handling routine. I didn't think about that when I copied the code but I also assume everyone is or should be handling errors and would know what to do to change the code in order to get it to work with their application. So, I think you are just picking on me on that point <img src=/S/crazy.gif border=0 alt=crazy width=15 height=15> I suppose there are too many comments in my code too? <img src=/S/blush.gif border=0 alt=blush width=15 height=15>

    I iether had a good reason at the time for not using the Replace funciton or I didn't realize it existed at the time I wrote the procedure. At this time I see no reason not to use the Replace function so, good point. Did you test my code against the Replace funciton for speed or are you just saying that Replace performs faster because it's built-in?

    As far as the double-quotes looking strange on the screen, I agree but this code is not intended to prepare the text for viewing on the screen. My understanding of the original request was to take a text string that was hand-entered by a user, that had a quote in it and insert it into a database record by using an INSERT statement with a VALUE list. It is true that single-quotes and double-quotes have special meaning to most database engines but the user doesn't need to know that. All of the processing goes on behind the form where the user will never see the addition of the second quote. If the record is read back into the form, the extra quote will have been properly removed by the database engine receiving it.

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

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    I only removed the comments from the version I posted to keep my reply compact. In itself it is a very good idea to comment code extensively!

    I didn't actually perform any tests. When processing a small number of records, you won't notice any difference between your version of the function and mine. But if you were to use it on very large numbers of records, there is bound to be a difference, since your code processes the string character by character and tests each of them, while Replace does it in one go (of course, Replace also goes through the characters, but that is done in compiled code, so it is *much* faster).

    My comment about not using the function to update the records themselves was mostly meant for others reading this thread; I assumed that you wouldn't use it that way. We have all kinds of members, from newbies to professional developers.

  8. #8
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    My appologies to you Hans. Because we are getting too far off of the original thread this is the last comment that I will make about the subject of commenting code but I want you to know that I wasn't taking a poke at anything you specifically did as far as commenting or not commenting your code examples. I was really just trying to be funny in a sarcastic way.

    Thanks for your replies.

    Patrick.

  9. #9
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Apostrophes in text fields (2003 (11.6566.6568) SP2)

    Here's another thought. Are you or can you use a bound form? Access handles appostrophe's very gracefully within a bound form.

Posting Permissions

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