By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,081 Members | 1,885 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,081 IT Pros & Developers. It's quick & easy.

how to deal with quotes and this sql statement

P: n/a
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


Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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 & '*'

Nov 12 '05 #2

P: n/a

"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
Nov 12 '05 #3

P: n/a
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)

Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.