The second method I've used for doing keyword searches is to create a
temporary table (tbl_KeyWords) which contains a single text field (KeyWord).
I then parse the individual elements of the key word text field into this
table (same method of parsing as in the previous post but use an append
query to append each word as a separate record in the table) and then create
a query that identifies the PK from my main table for each item that maps to
at least one of the words in tbl_KeyWords). I've got several different
versions of this query. The first version just counts the number of the key
words that appear in the [TextField], and looks like:
SELECT Temp.ID, Count(*) AS Priority
FROM (SELECT tbl_YourTable.ID
FROM tbl_YourTable, tbl_Key_Words
WHERE (((InStr(tbl_YourTable.[TextField],[KeyWord]))>0))) AS Temp
GROUP BY Temp.ID;
The subquery above creates a Cartesian join between your main table
(tbl_YourTable) and the key word table (tbl_Key_Word). It then checks to
find the first occurance of each key word in the string, and discards those
mappings where the key word is not found in the string. It then groups by
the PK field(ID) in tbl_YourTable, and counts the number of key words which
were found. This gives you the ability to both filter, and prioritize.
The second version, is a twist on the first, and it actually counts the
number of times each key word is found in each string. But to help in the
prioritization, it also adds 100 for each key word that is found instead of
just counting the number of words. It looks like:
SELECT Temp.ID, SUM(Temp.Occurances) as Priority
FROM (SELECT tbl_Key_Word_Test.ID,
100+(Len([TextField])-Len(Replace([TextField],[KeyWord],"")))/Len([KeyWord])
AS Occurances
FROM tbl_Key_Word_Test, tbl_Key_Words
WHERE
(((100+(Len([TextField])-Len(Replace([TextField],[KeyWord],"")))/Len([KeyWord]))<>100)))
as Temp
GROUP BY Temp.ID
With either of these techniques, you would not add anything to the WHERE
clause of your query, but would instead expand the syntax of the SELECT
portion of the query something like:
SELECT tbl_yourTable.*
FROM tbl_yourTable
INNER JOIN qryKeyWords
ON tbl_yourTable.ID = qryKeyWords.ID
WHERE .....
ORDER BY Priority DESC
Because of the Cartesian join, this method will take longer to run than
Method #1 (in the previous post), but it will allow you to prioritize your
results to return the "best" matches at the beginning of the query.
HTH
Dale
sebastico said:
Dale
Dealing with the keywords filter. In our case the keywords table is linked
to the PK of the main table. User may write keywords like: biodiversity,
biological corridor, buffer zone, biological connectivity, etc. We woud
like
only space to separate the keywords.
Could you suggest a way to realize my idea. Even if you have been working
with the plan of your previous message.
Many thanks
Dale Fye said:
Sebastico,
For the years, I would create a second textbox and label them
txtFilterStartYear and txtFilterEndYear. I would probably even setup the
GotFocus event of txtFilterEndYear to test and see whether
txtFilterStartYear
contained a value, and if not, would reset the focus to
txtFilterStartYear.
Then I would used code similar to what you already have to determine
whether
the user entered neither, one or both of those years. I would then build
that portion of the code based on that input, something like:
If isnull(me.txtFilterStartYear) and isnull(me.txtFilterEndYear) then
'do nothing
elseif isnull(me.txtFilterEndYear) then
strWHERE = strWhere & "([Year] = """ & me.txtFilterStartYear & """)
AND "
else
strWHERE = strWHERE _
& ([Year] >= """ & me.txtFilterStartYear & """ AND "
_
& [Year] <= """ & me.txtFilterEndYear & """) AND "
endif
Dealing with the key words is a bit more difficult, because the user
could
use almost anything (space, comma, ...) to separate the keywords, so the
first thing I would probably do is replace any punctuation in the keyword
field with a space. I would then start a loop to replace all of the
instances of a space followed by another space (" ") with a single
space,
and I would do this until no more double spaces exist; something like:
strKeyWords = me.txtFilterKeyWords
strKeyWords = replace(strKeyWords, ",", " ")
strKeyWords = replace(strkeywords, "-", " ")
Do While instr(strKeyWords, " ") > 0
strKeyWords = replace(strKeyWords, " ", " ")
Loop
Finally, I would use the Split( ) function to parse strKeyWords into
individual pieces, and add them to the filter string as appropriate.
Gotta go, will try to get to this section of the code later this evening.
----
HTH
Dale
:
Hello
I have a search Form (AllenBromne modified). I would like add two
filters,
one to filter between two years, and another to filter Keywords.
I have this code to Filter for Year
If Not IsNull(Me.txtFilterYear) Then
strWhere = strWhere & "([Year] = """ & Me.txtFilterYear & """) AND
"
End If
I would like to in this code to filter between years. User may enter
two years
I have this code to Filter by Keyword
If Not IsNull(Me.txtFilterKeyWordID) Then
strWhere = strWhere & "([KeyWordID] Like """ & Me.txt
txtFilterKeyWordID
& """) AND "
End If
I would like this code to filter by Keywords. User may enter two or
three
keywords.
Cold you help me?
Many thanks
.
