RecordSet Question: Bad luck of the Irish? (1 Viewer)

Randix

Registered User.
Local time
Today, 13:30
Joined
Mar 24, 2001
Messages
56
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

Registered User.
Local time
Today, 13:30
Joined
Jul 3, 2001
Messages
38
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,981
You need double quotes rather than single quotes around the text field if it can contain single quotes (apostrophes).

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

Randix

Registered User.
Local time
Today, 13:30
Joined
Mar 24, 2001
Messages
56
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:30
Joined
Feb 19, 2002
Messages
42,981
I add a module to each database so I can store global variables and constants in one easily found place.

Public Const QUOTE As String = """"

Then use the QUOTE constant wherever you need to embed double quotes.

Dim strSQL As String
strSQL = "select * from old where Left(field3, Len(field3) - 1) & Right(field2, Len(field2) - 1) = " & QUOTE & Left(Me.Field3, Len(Me.Field3) - 1) & Right(Me.Field2, Len(Me.Field2) - 1) & QUOTE & ";"

Set rst = db.OpenRecordset(strSQL)

I also changed the + signs to & and put the string into a variable where it will be easier to debug should that be necessary.
 

Users who are viewing this thread

Top Bottom