Search Problem 1

[52]

I made a simple search where I can search titles. I've made it so that "fat cat" will pull up "fatal catastrophy" or "fatal incident catastrophy"..... The search works as long as the title is in the order of the keywords to search by.

My problem is the words will not mix: If I type in "fat cat", It will not pull up "cat fat"

My search changes a string to *fat*cat*

Is there any way to make a search to "mix" the words so it searches for all words and not in a specified order so I can pull up all matches?

 

[katho]

simplest solution : get the entered text in a string and look in the string for the first blankspace. if there is one, break down this string in 2 strings.
then look for the next blankspace and so on.

Every time you encounter a blancspace, let a counter increment.

Then, search not only on the give string (with *), but repeat the search with every possible combination (this is a little test to see if you know the right mathematical function to find all possible combinations - you'll need the value of your counter-variable here!)

Hope this has helped you out.

 

[wemeier]

Are you doing your search by opening a query from a form or by applying a filter to the bound recordset of the form?

If you are applying a filter using code, do the following:

Dim intSpace as Integer
Dim strCriteria as String
Dim strSearchWords as String

strSearchWords = Trim((name of search criteria field))
strCriteria = ""
intSpace = InStr(strSearchWords," &quot
While intSpace > 0
strCriteria = strCriteria & " And Like '%" & Left(strSearchWords,intSpace - 1) & "%'"
strSearchWords = Trim(Mid(strSearchWords,intSpace))
intSpace = InStr(strSearchWords," &quot
Wend
strCriteria = "Like '%" & strSearchWords & "%'" & strCriteria

DoCmd.ApplyFilter ,strCriteria

What this coding does is builds criteria from each word in the form's search field. In your example, the criteria that is built would be:

[Title] Like "%fat%" And [Title] Like "%cat%". Any title matching BOTH Like conditions (in any order) will be returned.

If you're opening a query instead of applying a filter, put the final contents of strCriteria in a hidden text box on your form and refer to the text box in the query's Criteria.

 

[52]

wemeier - thank you - that works great!!!! (One thing - I had to use "*" instead of "%".

Thanks again!!!

 

[wemeier]

Sorry about the "%" -- I've been working in SQL Server too long! You're right -- the asterisk is the proper symbol to use.