Problem with apostrophe in code for searches

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

magmike - 26 May 2008 21:04 GMT

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

Reply to this Message

Douglas J. Steele - 26 May 2008 21:52 GMT

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

Signature

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

>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:
[quoted text clipped - 14 lines]
>
> magmike

Reply to this Message

magmike - 26 May 2008 22:34 GMT

On May 26, 3:52 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> Two approaches.
>
[quoted text clipped - 39 lines]
>
> - Show quoted text -

Thanks! Well done!

Reply to this Message