Function to parse Double and/or Single Quotes

[JackV]

I have an SQL statement in an app that I'm modifying in which the value for the WHERE condition can have a single or a double quote in it and though I haven't seen it yet, it's probably just a matter of time when that value will contain both a single AND double quote.

I can't seem to come up with a function to handle both and return a valid value without getting syntax errors.

Thanks in advance.

-Jack

[Joacim Andersson]

Public Function CleanString(sTheString As String) As String
    Dim sRetVal As String
    sRetVal = Replace(sTheString, "'", "''")
    sRetVal = Replace(sRetVal, """", "''")
End Function

This code replaces all single and double quotes with two single quotes witch works in SQL.

[JackV]

Thanks for the reply there Joacim,

This would work fine if I were inserting records but because this is in a where clause, the value that I would be trying to retrieve wouldn't come up because there would be no data to match it.

Here are 3 examples of sample WHERE criteria and how the data already sits in the database.

Example 1:
4" Master - 20 1/8 x 4 3/4 x 52 1/8

Example 2:
"DUMMY" SHELF BOX

Example 3:
12' CARDBOARD EASEL - 12 X 12 X 12



-JackV