Error due to sign " ' " in the query string

Hi, I wrote a VB6.0 program to access a Ms Access database using the following statement:

FindCrit="Select * From DB1 where Product like '*" & SProduct & "*' "

It works well but it produced error while SProduct="Children's Products".
How to fix the error caused by the sign " ' "? Please help.

FindCrit="Select * From DB1 where Product like '*" & REPLACE(SProduct, "'", "''") & "*' "

FindCrit="Select * From DB1 where Product like '*" & REPLACE(SProduct, "'", "''") & "*' "


Thanks! The problem is fixed.

Hi, I wrote a VB6.0 program to access a Ms Access database using the following statement:

FindCrit="Select * From DB1 where Product like '*" & SProduct & "*' "

It works well but it produced error while SProduct="Children's Products".
How to fix the error caused by the sign " ' "? Please help.


Well I was having the same problem in Access and I solved the problem by replacing the " ' " by its ASCII code. That would be in your problem:

FindCrit="Select * From DB1 where Product like " & Chr(34) & "*" & SProduct & "*" & Chr(34) [& ORDER BY ...]

the green text ( between [ ]) is only if you like to order your query.
I hope your problem is solved.

Either method will work. I becomes a matter of preference. I prefer to use the replace method, because you can replace OTHER characters and symbols within your table. So, I create a function that takes a string parameter. Then the replace() routine is run on the string, replacing any invalid characters.