Problem with apostrophe in code for searches

[magmike]

I have a search box, that when the search term contains an apostrophe
(i.e. O'Charley's), I get an error. Here is the code:

Private Sub findcoButton_Click()
Me.Label7.Visible = False
Me.EnterLead.Visible = False
Forms!FindCompanies!findquery.Form.Filter = "Company Like " & "'*"
& Me.findco & "*'"
Forms!FindCompanies!findquery.Form.FilterOn = True
Forms!FindCompanies!findquery.Form.Visible = True
Me.Command15.Visible = True
Me.findco.SetFocus
End Sub

I sort of understand the problem, but don't know how to fix it. Help,
and thanks!

magmike

 

[Douglas J. Steele]

Two approaches.

Forms!FindCompanies!findquery.Form.Filter = "Company Like " & """*" &
Me.findco & "*"""

which can be simplified to

Forms!FindCompanies!findquery.Form.Filter = "Company Like ""*" & Me.findco
& "*"""

or

Forms!FindCompanies!findquery.Form.Filter = "Company Like '*" &
Replace(Me.findco, "'", "''") & "*'"

See my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access" for more details. You can download the column (and sample database)
for free from http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[magmike]

Two approaches.

 Forms!FindCompanies!findquery.Form.Filter = "Company Like " & """*" &
Me.findco & "*"""

which can be simplified to

 Forms!FindCompanies!findquery.Form.Filter = "Company Like ""*" & Me.findco
& "*"""

or

 Forms!FindCompanies!findquery.Form.Filter = "Company Like '*" &
Replace(Me.findco, "'", "''") & "*'"

See my May, 2004 "Access Answers" column in Pinnacle Publication's "Smart
Access" for more details. You can download the column (and sample database)
for free fromhttp://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)









- Show quoted text -

Thanks! Well done!