MySQL: and Quotation Marks **Resolved**

How would I use the following string to data from a MySQL Table:

Mark's Computer

I have tried the following and no luck:

1) I have used the escape Character (Back Slash \) followed by a single
quote (\') as documented in the MySQL Help File - No Good
2) I also tried changing the entire string into Ascii - No Good
3) I Used the MySQL escape character again '\' and typed the Ascii code
after it - No Good

VB Code:

strSQL = "SELECT * FROM MyTable WHERE ComField ='[COLOR=Red]Mark's[/COLOR]'"

Any Ideas?

I forgot to add that I am able to save (insert) the following string without a problem:

Mark's Computer's Mouse

But when I attempt to retrieve that record I get the following error message:

VB Code:

Crit="Mark's"
 
strSQL = "SELECT * FROM MyTable WHERE ComField ='" & CleanText(Crit) & "'"
Public Function CleanText(strIn As String) As String
    On Error GoTo VBError
    Dim iAcnt As Long
    Dim strString As String
    Dim vLimit As Long
    vLimit = Len(strIn)
    For iAcnt = 1 To vLimit
        Select Case Asc(Mid$(strIn, iAcnt, 1))
        Case 10, 13
            strString = strString & Mid$(strIn, iAcnt, 1)
        Case 124
            strString = strString & "!"
        Case 39
            strString = strString & "''"
        Case 34
            strString = strString & """"
        Case Is < 32
            strString = strString & " "
        Case Is > 126
            strString = strString & " "
        Case Else
            strString = strString & Mid$(strIn, iAcnt, 1)
        End Select
    Next
    CleanText = strString
    CleanText = Trim$(CleanText)
Exit Function
VBError:
    MsgBox "VBError in Sub Parse_SQL_Text : " & Err.Number & " - " & Err.Description
    Resume Next
End Function

Quote:

---

Originally Posted by dee-u

VB Code:

Crit="Mark's"
 
strSQL = "SELECT * FROM MyTable WHERE ComField ='" & CleanText(Crit) & "'"
Public Function CleanText(strIn As String) As String
    On Error GoTo VBError
    Dim iAcnt As Long
    Dim strString As String
    Dim vLimit As Long
    vLimit = Len(strIn)
    For iAcnt = 1 To vLimit
        Select Case Asc(Mid$(strIn, iAcnt, 1))
        Case 10, 13
            strString = strString & Mid$(strIn, iAcnt, 1)
        Case 124
            strString = strString & "!"
        Case 39
            strString = strString & "''"
        Case 34
            strString = strString & """"
        Case Is < 32
            strString = strString & " "
        Case Is > 126
            strString = strString & " "
        Case Else
            strString = strString & Mid$(strIn, iAcnt, 1)
        End Select
    Next
    CleanText = strString
    CleanText = Trim$(CleanText)
Exit Function
VBError:
    MsgBox "VBError in Sub Parse_SQL_Text : " & Err.Number & " - " & Err.Description
    Resume Next
End Function

---

Thanks, you solved the problem!!!

EDIT - What about ? / \ ` etc?