RecordSet Question: Bad luck of the Irish?

[Randix]

I have the following line in my code...

Set rst = db.OpenRecordset("select * from old where trim([field3])+trim([field2]) = '" & Trim(Me.Field3) + Trim(Me.Field2) & "';")

It works fine EXCEPT when it gets to a name that has a "'" in it, such as O'Dell, etc. Can anyone suggest a solution?

 

[disgruntled]

this will take care of it if you wrap it aroud anything where the problem may occur.

ex. '" & SingleQuoteSearch(ClientName1.Value) & "'

Public Function SingleQuoteSearch(ByVal searchString As Variant)
'the function searches for single quotes, and
'replaces them with a blank space so that
'SQL query can be run successfully

Dim Position As Integer
Dim SearchChar As String
Dim FinalString As String
Dim EndString As String
Dim TempString As String
Dim BeginString As String

SearchChar = "'"

Position = InStr(1, searchString, SearchChar, 1)
If (Position = 0) Then
FinalString = searchString
Else
Do While Position <> 0
BeginString = Mid(searchString, 1, Position - 1)
EndString = Mid(searchString, Position + 1)
TempString = BeginString & " "
FinalString = FinalString & TempString
searchString = EndString
Position = InStr(1, searchString, SearchChar, 1)
Loop
FinalString = FinalString & EndString
End If

SingleQuoteSearch = FinalString
End Function

 

[Randix]

Pat...back in August you posted the suggestion to put double quotes rather than single quotes around a text field if it can contain single quotes (apostrophes)...

Here's my current line of code:

Set rst = db.OpenRecordset("select * from old where Left(field3, Len(field3) - 1) + Right(field2, Len(field2) - 1) = '" & Left(Me.Field3, Len(Me.Field3) - 1) + Right(Me.Field2, Len(Me.Field2) - 1) & "';")

How would I do it to both sides of the above line of code?

Steve