Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 & "' . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert single quote symbol | Excel Worksheet Functions | |||
vlookup and using single quote | Excel Worksheet Functions | |||
How do I display a single quote in a cell? | Excel Discussion (Misc queries) | |||
using a single quote ' in SEARCH function | Excel Worksheet Functions | |||
Remove single quote | Excel Worksheet Functions |