Parse Single and Double Quotes from SQL String

[BikeToWork]

I have set up a form that filters based on the selection in a dropdown box. If the filter is [Subject] = 'subject', that works properly. However, if the filter sql string is [Subject]='isn't subject', that generates an error and the form will not filter. I tried the following without success:

Public Function CheckForQuotes(someText) As Variant 
 
    If IsNull(someText) Then
        CheckForQuotes = Space(1)
        Exit Function
    End If
 
Dim chrSingleQuote      As String: chrSingleQuote = "'"
Dim chrDoubleQuote      As String: chrDoubleQuote = """"
Dim start%, length%, remainder%, x%
Dim strPart1, strRemainder  As String
Dim strBase, strNew         As Variant
 
    start% = 1
    strBase = someText
    strNew = ""
 
    Do
        x% = InStr(start%, strBase, chrSingleQuote, vbTextCompare)
 
        If x% <> 0 Then
            strPart1 = Mid(strBase, start%, x% - start%)
            strNew = strNew & strPart1
            strNew = strNew & "''"
            start% = x% + 1
        End If
    Loop Until x% = 0
 
    strNew = strNew & Mid(strBase, start%)
 
    CheckForQuotes = strNew
End Function
 

Any advice on this is much appreciated.

[Rabbit]

Why check for quotes? Why not replace them outright whether or not they're there?

Replace(value, "'", "''")

[Seth Schrock]

If all you are trying to do is remove double and single quotes from a string, then just use the replace function.

Dim myString as String
 
myString = "Isn't Filtered"
myString = Replace(myString, "'", "")
myString = Replace(myString, """", "")
 
Debug.Print myString
'myString now equals "Isnt Filtered"

[BikeToWork]

Thanks for the response, Rabbit. Actually, I need the string with the single or double quote in it but "escaped". For example, if I am trying to filter the form on a field with [Subject] = 'This isn't a subject', I need to filter on the literal string with the single quote but the compiler doesn't know that and interprets the single quote as a string delimiter. So it needs to be like [Subject] = 'This isn''t a subject'. I hope I am making sense.

[Rabbit]

What I posted will escape it

[BikeToWork]

The problem with just using the replace function is that it will change [Subject]= 'doesn't matter' with [Subject]= ''doesn''t matter'', where the first and last single quote string delimiters are replaced with two single quotes. This will not compile. I need to retain the leading and ending string delimiter single quotes and just replace the single quote in "doesn't matter" with two single quotes so the whole string is [Subject] = 'doesn''t matter'. Thanks in advance for any help with this.

[Rabbit]

Not if you run the replace on the value before you append and prepend the starting and ending quotes.

[BikeToWork]

Rabbit, thank you for your help. At the risk of sounding ignorant, how would one go about removing the single quotes at the beginning and end of the string before replacing the single quote embedded in the string with ''? For example, how would you turn [Subject] = 'doesn't matter' into [Subject] = doesn't matter. From that point I can see how replace would work on the embedded single quote. I just need to massage the string to begin with so it does not have the beginning and ending single quotes. Thanks in advance.

[Rabbit]

strValue = "o'connor"
strSQL = "SELECT * FROM tableName WHERE " & _
   "fieldName = '" & Replace(strValue, "'", "''") & "'"