|
| |
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
 |
Is it possible to search a memo field..... - 12/16/2005 17:43:37
...for a word(s).
If so how do I get started, is it a variation of a Like query?
Thanks
|
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Is it possible to search a memo field..... - 12/16/2005 17:50:55
What database?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Is it possible to search a memo field..... - 12/16/2005 17:52:09
Access.
The pages would be asp.
Dave
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Is it possible to search a memo field..... - 12/16/2005 18:07:33
Here is something that has worked for me. In my example, the text box for my search form is named "Notes". I first assign it a value if the user submits it empty. I chose xxxyyyzzz assuming that would never exist in any of the records being searched. I then create an array by using the Split function on spaces so the number of items in the array is the number of words in the search. Finally, I use a for/next loop so that for every word in the array, I add to my SQL of "SELECT * FROM MeetingNotes WHERE ", "AND dbField LIKE '%a_word_in_the_array%'. That works for what I need. If I get ambitious one day I will refine it so the user can do exact phrase searching as well, but not today. Here is a snippet of my code:
If Request.Form("Notes") = "" then
arrSearchWords=Split("xxxyyyzzz"," ")
Else
arrSearchWords=Split(Request.Form("Notes")," ")
End If
Qry="SELECT * FROM MeetingNotes WHERE "
For f = 0 to Ubound(arrSearchWords)
if f > 0 then Qry = Qry & "AND "
Qry = fp_sQry & "Notes LIKE '%" & arrSearchWords(f) & "%' "
next
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Is it possible to search a memo field..... - 12/16/2005 18:26:41
So a couple of things:
So if the user entered : dave bob
-----------------------------------------------------
Why this section?
If Request.Form("Notes") = "" then
arrSearchWords=Split("xxxyyyzzz"," ")
Else
---------------------------------------------------------
So if the user entered : dave bob the split separates these two words correct?
arrSearchWords=Split(Request.Form("Notes")," ")
End If
--------------------------------------------------------------------------------------------------
The Ubound(arrSearchWords) counts these words as 2
Qry="SELECT * FROM MeetingNotes WHERE "
For f = 0 to Ubound(arrSearchWords)
----------------------------------------------------------------------
Why this?
if f > 0 then Qry = Qry & "AND "
-------------------------------------------------------------------------------------
The % on either side of the key word allows it to look inside the memo field right?
Qry = fp_sQry & "Notes LIKE '%" & arrSearchWords(f) & "%' "
next
Thanks
| |
|
|
|
BeTheBall
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Is it possible to search a memo field..... - 12/16/2005 18:35:21
quote:
ORIGINAL: DaveKstl
So a couple of things:
So if the user entered : dave bob
-----------------------------------------------------
Why this section?
If Request.Form("Notes") = "" then
arrSearchWords=Split("xxxyyyzzz"," ")
Else
---------------------------------------------------------
This section deals with the user who clicks submit without entering anything. Otherwise the page will error out.
quote:
So if the user entered : dave bob the split separates these two words correct?
arrSearchWords=Split(Request.Form("Notes")," ")
End If
--------------------------------------------------------------------------------------------------
Correct
quote:
The Ubound(arrSearchWords) counts these words as 2
Qry="SELECT * FROM MeetingNotes WHERE "
For f = 0 to Ubound(arrSearchWords)
----------------------------------------------------------------------
also correct
quote:
Why this?
if f > 0 then Qry = Qry & "AND "
-------------------------------------------------------------------------------------
The first time through the loop, f is equal to zero. It is also the first word in the array, so we don't want to add the word "AND" yet because there is not second criteria. Does that make sense?
quote:
The % on either side of the key word allows it to look inside the memo field right?
Qry = fp_sQry & "Notes LIKE '%" & arrSearchWords(f) & "%' "
next
Thanks
Correct.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Is it possible to search a memo field..... - 12/16/2005 18:47:14
Thank you!
I appreciate this.
Dave
| |
|
|
|
dpf
Posts: 7126
Joined: 11/12/2003
From: India-napolis
Status: offline
|
RE: Is it possible to search a memo field..... - 12/17/2005 10:38:11
brilliant duane! and great question to clarify, dave. I saved that one..<smile>
_____________________________
Dan
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
