Re: Apostrophie Problem

On Wed, 17 Jan 2001 20:10:24 -0000, "Perry Hemmings" <perry.hemmings_at_btinternet.com> wrote:

>Hello All
>
>I am running Oracle 8 with a Visual Basic frontend, my program falls over
>when an Apostrophe is in the name i.e. O'Brien and my search needs adjusting
>can anyone advise me on how to achieve this??
>
>Res = "SELECT Lastname FROM Customer WHERE Lastname = txtTEXTBOX"
>
>Thanks in advance
>
>Perry
>
>Please e-mail your response to perry.hemmings_at_volteurope.com as well as
>responding to the NG
>
>

You need to double the apostrophies.

For example:

Res = "SELECT Lastname FROM Customer WHERE Lastname = '" & Replace_String(txtTEXTBOX, "'", "''") & "'"

In Access, you can quote values via square brackets as well "[]". Don't know about VB.

HTH,
 Brian

Function Replace_String(Word As String, What_To_Replace As String, Replace_With As String) As String

    ' This function will accept Word, and replace any occurences of What_To_Replace with

    ' Replace_With that are inside it.     

    Dim Length_Of_Word As Integer, Length_Of_What_To_Replace
    Dim String_Location As Integer, Start_Search_At As Integer
    Dim Before_Word As String, After_Word As String
     

    Length_Of_Word = Len(Word)
    Length_Of_What_To_Replace = Len(What_To_Replace)     

    ' default start to first character.
    Start_Search_At = 1     

    Do

       
       ' Find the word in the string
       String_Location = InStr(Start_Search_At, Word, What_To_Replace)
            
        ' If it wasn't found, then there is no reason to loop.
        If String_Location = 0 Then Exit Do
            
        ' Grab everything before it
        Before_Word = Mid(Word, Start_Search_At, String_Location -
Start_Search_At)
        
        ' Grab everything after it
        After_Word = Mid(Word, String_Location +
Length_Of_What_To_Replace)
        
        ' Set the return to itself and any other befores. Not the
after
        ' because it may loop again and become a "before".
        Replace_String = Replace_String & Before_Word & Replace_With
        
        'Next time start the search after what was already replaced.
        Start_Search_At = String_Location + Length_Of_What_To_Replace
    

    Loop

    ' Add on the ending that was not used before.     Replace_String = Replace_String & After_Word     

    ' If nothing was replaced, then return the word itself.     If Replace_String = "" Then Replace_String = Word

End Function Received on Wed Jan 17 2001 - 15:14:51 CST