Run-time error 80040e14 (Missing Operator) in '% ... Writer's Choice .... %"

[WDSnews]

I think I know the problem. I don't know the solution. Here's my code...

strSQL = "SELECT * FROM [Item Types] WHERE [Title] like '%" &
strTestPhrase & "%'"
rstItems.Open Source:=strSQL, ActiveConnection:=cnnAllotment, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

Here's the strTestPhrase as interpretted by the error message...
'[Title] like '%Glencoe Writer's Choice Joshual Edel%"

Here's the error message...
Syntax error (missing operator) in query expression '[Title] like
'%Glencoe Writer's Choice Joshual Edel%".

I believe it's the apostrophe in the word Writer's that triggers this one.
How can I change my code to accomodate this situation? BTW, I'm searching
book titles which means the title could contain most anything. The other
thing that got my attention are the two spaces before Joshual.

thank you for your suggestions.

 

[RoyVidar]

I think I know the problem. I don't know the solution. Here's my
code...

strSQL = "SELECT * FROM [Item Types] WHERE [Title] like '%"
& strTestPhrase & "%'"
rstItems.Open Source:=strSQL,
ActiveConnection:=cnnAllotment, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

Here's the strTestPhrase as interpretted by the error message...
'[Title] like '%Glencoe Writer's Choice Joshual Edel%"

Here's the error message...
Syntax error (missing operator) in query expression '[Title] like
'%Glencoe Writer's Choice Joshual Edel%".

I believe it's the apostrophe in the word Writer's that triggers this
one. How can I change my code to accomodate this situation? BTW, I'm
searching book titles which means the title could contain most
anything. The other thing that got my attention are the two spaces
before Joshual.

thank you for your suggestions.

Try to doubling up the apostrophe, i e

....like '%" & Replace(strTestPhrase, "'", "''") & "%'"

i e, replace one occurrence of apostrophe with two

 

[WDSnews]

I'll try it. thank you.

I think I know the problem. I don't know the solution. Here's my
code...

strSQL = "SELECT * FROM [Item Types] WHERE [Title] like '%" &
strTestPhrase & "%'"
rstItems.Open Source:=strSQL, ActiveConnection:=cnnAllotment, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

Here's the strTestPhrase as interpretted by the error message...
'[Title] like '%Glencoe Writer's Choice Joshual Edel%"

Here's the error message...
Syntax error (missing operator) in query expression '[Title] like
'%Glencoe Writer's Choice Joshual Edel%".

I believe it's the apostrophe in the word Writer's that triggers this
one. How can I change my code to accomodate this situation? BTW, I'm
searching book titles which means the title could contain most anything.
The other thing that got my attention are the two spaces before Joshual.

thank you for your suggestions.

Try to doubling up the apostrophe, i e

...like '%" & Replace(strTestPhrase, "'", "''") & "%'"

i e, replace one occurrence of apostrophe with two