Error due to sign " ' " in the query string

Paulchow
04-18-2004, 11:35 PM
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.

wengwashere
04-19-2004, 12:06 AM
FindCrit="Select * From DB1 where Product like '*" & REPLACE(SProduct, "'", "''") & "*' "

Paulchow
04-19-2004, 12:42 AM
FindCrit="Select * From DB1 where Product like '*" & REPLACE(SProduct, "'", "''") & "*' "


Thanks! The problem is fixed.

YaRoBi
04-19-2004, 04:45 AM
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.

MKoslof
04-19-2004, 08:37 AM
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.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum