INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

syntax help to be able to catch names with an apostrophe

syntax help to be able to catch names with an apostrophe

(OP)
Hi,

I have a form where a user can enter a name to search for and another form opens with the results of the search. The search does not work if a name that is being searched has an apostrophe, such as O'Leary and I know it's because I don't have the syntax correct in the code below, but I can't seem to figure it out. Thanks for your help!


.Source = "SELECT timage.* " _
& " FROM timage " _
& " WHERE InStr(Keywords, '" & Forms![search options].searchtext & "') > 0 "

RE: syntax help to be able to catch names with an apostrophe

Replace a single quote with 2 single quotes:

Source = "SELECT timage.* " _
& " FROM timage " _
& " WHERE InStr(Keywords, '" & Replace(Forms![search options].searchtext, "'", "''") & "') > 0 "

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: syntax help to be able to catch names with an apostrophe

(OP)
Thanks so much!!

RE: syntax help to be able to catch names with an apostrophe

atadbitslow,
Click the "Great Post" link to show your appreciation to Andy for providing a prompt and accurate response and to mark the question as answered.

Duane
Vevey, Switzerland
Hook'D on Access
MS Access MVP 2001-2016

RE: syntax help to be able to catch names with an apostrophe

(OP)
Thanks Duane.....I had clicked on "Great Post" yesterday, but missed the link to confirm it......done now!

RE: syntax help to be able to catch names with an apostrophe

Thanks.

You may also consider your SQL like this:

Source = "SELECT timage.* " _
& " FROM timage " _
& " WHERE Keywords LIKE '*" & Replace(Forms![search options].searchtext, "'", "''") & "*')"

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: syntax help to be able to catch names with an apostrophe

(OP)
Thanks! Most appreciated!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close