syntax help to be able to catch names with an apostrophe 1

[atadbitslow]

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 "

 

[Andrzejek]

Replace a single quote with 2 single quotes:
[tt]
Source = "SELECT timage.* " _
& " FROM timage " _
& " WHERE InStr(Keywords, '" & [blue]Replace([/blue]Forms![search options].searchtext[blue], "'", "''")[/blue] & "') > 0 "
[/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[atadbitslow]

Thanks so much!!

 

[dhookom]

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

 

[atadbitslow]

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

 

[Andrzejek]

Thanks.

You may also consider your SQL like this:
[tt]
Source = "SELECT timage.* " _
& " FROM timage " _
& " WHERE Keywords [blue]LIKE '*[/blue]" & Replace(Forms![search options].searchtext, "'", "''") & "[blue]*'[/blue])" [/tt]

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[atadbitslow]

Thanks! Most appreciated!