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

Keyword(s) query.

P: n/a
My management requested that I make my search engine more user friendly
by "Making it work like Google's". They don't like that they have to
enter a string exactly in the order it's in when searching on a memo
field.

I was wondering if it were possible to run a query that would search a
field on keywords in any order rather than a set consecutive string.
What I mean is typing in "One Two Three" would find "One" or "Three" or
"Two One" rather than only "One Two Three" or "One Two Three Four"

Only way I can think to do it is to parse out the string into separate
words, run separate queries for each word, then somehow link and rank
the results according to which had the most hits.

Is there any way to do this without choking an A97 DB's performance to
death?

Jun 5 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Okay, here's how I did it.
Create a search form with a place to put all the keywords. On the
AfterUpdate of this field palce this dirty code:
Private Sub Text1_AfterUpdate()
Dim Wrd1, Wrd2, Wrd3, Wrd4, Wrd5, Wrd6, Wrd7, Wrd8 As String

Dim Lett(250) As String
Dim Word(20) As String
j = 1
For i = 1 To Len(Me.Text1)
Lett(i) = Mid(Me.Text1, i, 1)

If Lett(i) = " " Then
j = j + 1
Else
Word(j) = Word(j) + Lett(i)
Select Case j
Case 1
If Word(j) > " " Then Wrd1 = Word(j)
Me!Wrd1 = Wrd1
Case 2
If Word(j) > " " Then Wrd2 = Word(j)
Me!Wrd2 = Wrd2
Case 3
If Word(j) > " " Then Wrd3 = Word(j)
Me!Wrd3 = Wrd3
End Select
End If
Next i

End Sub

I have code for only three words but you can add as many as you want.

I have enought invisible fields on the form for Wrd1, Wrd2, etc.
Behind the search button on the form i open the following query (You can
open a form based on this query if you want):

SELECT Injuries.recno, Injuries.InjuryType, Injuries.Name, Injuries.Date,
Injuries.Criteria, Injuries.InjDept, Injuries.BodyPart, Injuries.Shift,
Injuries.Description, [Criteria] & [BodyPart] & [Description] AS LU
FROM Injuries
WHERE ((([Criteria] & [BodyPart] & [Description]) Like "*" & [Forms]!
[LookupInjury]![Wrd1] & "*" And ([Criteria] & [BodyPart] & [Description])
Like "*" & [Forms]![LookupInjury]![Wrd2] & "*" And ([Criteria] & [BodyPart] &
[Description]) Like "*" & [Forms]![LookupInjury]![Wrd3] & "*"));

As you can see this looks up injuries and I want them to be able to look up
nbased on a Criteria, the body part injured and the type of injury.

Hope this dirty way of doing it helps. It may not look pretty but is
adequate and that is all that matters as far as management is concerned!

An***********@bcbsmn.com wrote:
My management requested that I make my search engine more user friendly
by "Making it work like Google's". They don't like that they have to
enter a string exactly in the order it's in when searching on a memo
field.

I was wondering if it were possible to run a query that would search a
field on keywords in any order rather than a set consecutive string.
What I mean is typing in "One Two Three" would find "One" or "Three" or
"Two One" rather than only "One Two Three" or "One Two Three Four"

Only way I can think to do it is to parse out the string into separate
words, run separate queries for each word, then somehow link and rank
the results according to which had the most hits.

Is there any way to do this without choking an A97 DB's performance to
death?


--
Message posted via http://www.accessmonster.com
Jun 5 '06 #2

P: n/a
thanks for the tip, and sorry for the late reply. I went on vacation
for a few days immediatly after posting this and forgot I posted it.

I think this will work for the application.

Jun 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.