469,607 Members | 1,953 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,607 developers. It's quick & easy.

how to deal with quotes and this sql statement

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
3 2237
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

"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
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.

Similar topics

3 posts views Thread by J. Franchino | last post: by
5 posts views Thread by Joel | last post: by
3 posts views Thread by Stefania Scott | last post: by
1 post views Thread by swep | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.