Stripping Non Ascii (1 Viewer)

[alexfwalker81]

I'm using the code below to strip certain non ascii characters in a query. It works nicely, apart from where it comes across a single quote. I understand why it's breaking, but I don't understand how I'd alter the function below to cope with single quotes in the fields that it's looking at;

Public Function fReplaceNonAscii(ByVal strInput As Variant) As Variant
Dim strSQL As String
strInput = strInput & vbNullString
If Len(strInput) > 0 Then
    strSQL = "SELECT T.* FROM tbl_ascii AS T " & _
            "WHERE INSTR(1,'" & strInput & "', T.[NonAscii]) > 0"
    With CurrentDb.OpenRecordset(strSQL, _
            dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            strInput = Replace(strInput, !NonAscii, !Ascii)
            .MoveNext
        Loop
    End With
    
End If
fReplaceNonAscii = strInput
End Function

 

[MajP]

I am assuming it breaks in the where. Is that the problem. I would first replace any ' with double singles.
strInput = strInput & vbNullString
strInput = replace(strInput,"'","''")

 

[alexfwalker81]

I am assuming it breaks in the where. Is that the problem. I would first replace any ' with double singles.
strInput = strInput & vbNullString
strInput = replace(strInput,"'","''")

Works like a charm - thank you!