Single quote in a where clause
 

Hi
2 simple functions:

Function adhHandleQuotes(ByVal varValue As Variant, ByVal
strDelimiter As String) As Variant

' Replace all instances of strdelimiter with varValue
with TWO instances,
' thereby handling the darned quote issue once and
for all.

' Returns Null if varValue was Null, otherwise
' returns varValue with all instances of strDelimiter
' replaced with two of each.
' adhHandleQuotes("This 'is' a test", "'") returns
' "This ''is'' a test"

adhHandleQuotes = adhReplace(varValue, strDelimiter,
strDelimiter & strDelimiter)
End Function

Function adhReplace(ByVal varValue As Variant, ByVal
strFind As String, ByVal strReplace As String) As Variant

' Replace all instances of strFind with strReplace in
varValue.

Dim intLenFind As Integer
Dim intLenReplace As Integer
Dim intPos As Integer

If IsNull(varValue) Then
adhReplace = Null
Else
intLenFind = Len(strFind)
intLenReplace = Len(strReplace)

intPos = 1
Do
intPos = InStr(intPos, varValue, strFind)
If intPos 0 Then
varValue = Left(varValue, intPos - 1) &
strReplace & Mid(varValue, intPos + intLenFind)
intPos = intPos + intLenReplace
End If
Loop Until intPos = 0
End If
adhReplace = varValue
End Function


-----Original Message-----

The SQL where clause fails because variableType has a
value that contains a single quote. Using amber signs and
double quotes worked in VB 6.0.

variableType = A'DVANCE

Sql = "Select Type From Equipment"
Sql = Sql & "WHERE Type = '" & variableType & "'

.