Single Quotes Removing 2

[celtc]

Here are two functions to help with Single Quotes.
The problems are caused by the character with ascii 39, and there is another character that looks very like it, ascii 96 which causes no problems.
FixApostrophe replaces all the char 39 with char 96.
QorN is for preparing strings to be put in SQL Statements (but not when they feed parameters on stored procs)

Public Function QOrN(xstr As Variant) As String
If xstr = "" Or IsNull(xstr) Then
QOrN = "Null"
Exit Function
Else
xstr = FixApostrophe(xstr)
QOrN = "'" & xstr & "'"
End If
End Function

Public Function FixApostrophe(xstr As Variant) As String
Dim posint As Integer
Dim lenint As Integer
Dim dstr As String
If IsNull(xstr) Then xstr = ""
dstr = xstr
lenint = Len(dstr)
posint = 1
While posint <> 0
posint = InStr(dstr, &quot;'&quot
If posint <> 0 Then
dstr = Left(dstr, posint - 1) & Chr(96) & Right(dstr, lenint - posint)
End If
Wend
FixApostrophe = dstr
End Function

 

[nickjar2]

That worked perfectly. What I have always done in the past is do a search and replace for all ' and replace with nothing. so o'leary became oleary. Very bad I know, I could never be bothered to write a function that would remove/replace the single quote for use in SQL.

I will use this little func in the future. Nick (Everton Rool OK!)

 

[mp9]

Here's another version of CeltC's FixApostrophe function that should be a little faster. It's reliant on running Access 2000 or above though (for the Replace function).

Function FixApostrophe(str As Variant) As String
If IsNull(str) = False Then
    FixApostrophe = Replace(str, "'", "`", , , vbTextCompare)
Else
    FixApostrophe = vbNullString
End If
End Function

HTH

http://www22.brinkster.com/accessory/

 

[gmcevoy]

This will maintain your Apostrophe properly without 30 lines of code.

str = Replace(str, "'", chr(34))

or

str = Replace(str, "'", "''")