Select causes a problem when a record contains " ' "

[joejoe]

When Selecting a record using the Select Statement it returns an error if the record contains an " ' ".

Ie.

Select * From [Banks] Where [Banks] ='" & Me.Text1.Text & "'"

If the bank was called " John's Bank " the system only reads up to the apostophe. so it searches for "John".

Is there any way around this as it is causing me grief

Thanks

[Clunietp]

Hi Joe

use the VB6 Replace function, and replace all single quotes with 2 single quotes (NOT a double quote)

example:

John's Bank

should be:

John''s Bank

before you send it to be processed.
So use this:

Select * From [Banks] Where [Banks] ='" & replace(Me.Text1.Text, "'", "''") & "'"

[Negative0]

If you are not using VB6 or want a different way you could these functions:

Fix quotes fixes a text field, and fixformquotes fixes all text fields on the form

Public Sub FixFormQuotes(glCallfrm As Form)
Dim mycontrol As Control
For Each mycontrol In glCallfrm.Controls
If TypeOf mycontrol Is TextBox Then
  mycontrol.Text = FixQuotes(mycontrol.Text)
End If
Next

End Sub
Public Function FixQuotes(A As String)

Dim i As Integer
i = InStr(A, "'")

Do While i > 0
  A = Left(A, i) & Mid(A, i)
  i = InStr(i + 2, A, "'")
Loop

FixQuotes = A

End Function