how to deal with quotes and this sql statement

[Danny]

Here is the sql statement.

Set rs2 = db.OpenRecordset("select * from Names where InStr(descriptions" +
"+" + """" + "|" + """" + "," + """" + asearchkey + """" + ") > 0")
this searches a recorset and returns all the names that have asearchkey
somewhere in their description

so if "hello" is in "this is a hello test" will be returned
but this fails - "the item is 3" tall" fails becaus of the quote

How can I successfully search this field when their are quotes but leave the
database as is so I dont have to get rid of the quotes before I do the query
I dont want to do that.

what about the ' character too, I didnt test that.

Thanks

[Salad]

Danny wrote:

Here is the sql statement.

Set rs2 = db.OpenRecordset("select * from Names where InStr(descriptions" +
"+" + """" + "|" + """" + "," + """" + asearchkey + """" + ") > 0")
this searches a recorset and returns all the names that have asearchkey
somewhere in their description

so if "hello" is in "this is a hello test" will be returned
but this fails - "the item is 3" tall" fails becaus of the quote

How can I successfully search this field when their are quotes but leave the
database as is so I dont have to get rid of the quotes before I do the query
I dont want to do that.

what about the ' character too, I didnt test that.

Thanks

Have you considered the LIKE operator
Where Description Like "*" & aSearchKey & "*"
or
Where Description Like '*' & aSearchKey & '*'

[Danny]

"Salad" <oi*@vinegar.com> wrote in message
news:j3******************@newsread1.news.pas.earth link.net...

Danny wrote:

Here is the sql statement.

Set rs2 = db.OpenRecordset("select * from Names where InStr(descriptions" + "+" + """" + "|" + """" + "," + """" + asearchkey + """" + ") > 0")
this searches a recorset and returns all the names that have asearchkey
somewhere in their description

so if "hello" is in "this is a hello test" will be returned
but this fails - "the item is 3" tall" fails becaus of the quote

How can I successfully search this field when their are quotes but leave the database as is so I dont have to get rid of the quotes before I do the query I dont want to do that.

what about the ' character too, I didnt test that.

Thanks

Have you considered the LIKE operator
Where Description Like "*" & aSearchKey & "*"
or
Where Description Like '*' & aSearchKey & '*'

Thanks,

I tried tihs and it seems to produce the same results without errors so it
does work, thank you
But this command is soo much slower than the instr command. I am dealing
with thousands of records and hte speed makes a difference.

Do you have any other suggestions?
How could I speed sometihng like this up?
Is there a way I can modify how I use the instr search command
Thanks in advance

[Salad]

Danny wrote:

"Salad" <oi*@vinegar.com> wrote in message
news:j3******************@newsread1.news.pas.earth link.net...

Danny wrote:

Here is the sql statement.

Set rs2 = db.OpenRecordset("select * from Names where
InStr(descriptions" +
"+" + """" + "|" + """" + "," + """" + asearchkey + """" + ") > 0")
this searches a recorset and returns all the names that have asearchkey
somewhere in their description

so if "hello" is in "this is a hello test" will be returned
but this fails - "the item is 3" tall" fails becaus of the quote

How can I successfully search this field when their are quotes but leave
the
database as is so I dont have to get rid of the quotes before I do the
query
I dont want to do that.

what about the ' character too, I didnt test that.

Thanks

Have you considered the LIKE operator
Where Description Like "*" & aSearchKey & "*"
or
Where Description Like '*' & aSearchKey & '*'

Thanks,

I tried tihs and it seems to produce the same results without errors so it
does work, thank you
But this command is soo much slower than the instr command. I am dealing
with thousands of records and hte speed makes a difference.

Do you have any other suggestions?
How could I speed sometihng like this up?
Is there a way I can modify how I use the instr search command
Thanks in advance

Your line with all the plusses and bars and quotes was simply too hard
to comprehend for my limited time.

I usually do something like
Dim strSQL As STring
strSQL = "Select * From Names Where " & _
"Instr(Description,asearchkey) > 0"
Set rs2 = db.OpenRecordset(strSQL,dbopensnapshot)