Multiple Keyword Search (1 Viewer)

[Vassago]

This is my first post here so bear with me.

What I am trying to accomplish is a mulitple search engine for records on one table in my database.

Basically what I want to accomplish is what user listo accomplished here http://www.access-programmers.co.uk...eferrerid=11730 but didn't explain how.

I will have a form with a text box, if the user inputs "Macromedia Flash" and hits the search button, I was all records with the words "macromedia" or "flash" to be in the search results.

example results would be:

Macromedia Flash MX
Macromedia Dreamweaver MX
Flash maker v3.5

all because they contain at least one of those two words.

He basically had the code I want, but didn't supply it in that post, as he was asking a question.

Please help.

Vassago

 

[ListO]

You're right, I was asking a question, and the code was irrelevant to that one.

I'll make a disclaimer first that I think this is kind of crude programming, and feels rather brute-force, but it does work. Maybe someone else will have some sweet refinements...

This code is called from a form on which resides an option group called AndOrButton which is 1 if AND, 2 if OR. There's also an option group called SearchTypeButtons which returns 1 to do a search among a description field, 2 to do a search among a cross-reference field, and 3 to do a search among the SoundID field. A button called SearchButton triggers this code.

The code builds a search filter string which is bound to a textbox on the form called SearchKW. There must be one, and only one space between words (I never made this code bullet-proof, but if it's going out into the user world, it must be made better). The code works through the input string one character at a time, each time it finds a space it assumes the preceding string of characters was a word and puts quotes around it, etc. See if it makes any sense to anyone.

Private Sub SearchButton_Click()
Dim X As Integer, Last As Integer, AndOr As String
Dim Quote As String, SearchField As String

'Strip leading and ending spaces
[SearchInput] = Trim([SearchInput]) 

Quote = Chr$(34)

Select Case [SearchTypeButtons]
    Case 1
        SearchField = "[Description]"
    Case 2
        SearchField = "[CrossRefID]"
    Case 3
        SearchField = "[SoundID]"
End Select

[SearchKW] = SearchField + " LIKE " + Quote + "*"

 If AndOrButton = 1 Then
    AndOr = " AND " + SearchField + " LIKE " + Quote + "*"
 Else
    AndOr = " OR " + SearchField + " LIKE " + Quote + "*"
 End If

Last = Len([SearchInput])

For X = 1 To Last
    If Mid$([SearchInput], X, 1) = " " Then
        [SearchKW] = [SearchKW] + "*" + Quote
        If X <> Last Then [SearchKW] = [SearchKW] + AndOr
    Else
        [SearchKW] = [SearchKW] + Mid$([SearchInput], X, 1)
    End If
Next X
SearchKW = SearchKW + "*" + Quote
If SearchKW = "*" Then SearchKW = ""

Me.Filter = SearchKW
Me.FilterOn = True


ExitSearchButton_Click:

End Sub

Good luck.

-Curt

PS: A note to new users such as yourself - try not to post the same question to different sections of the forum, especially on the same day. It diffuses the answers and just ticks some people off. Good luck.

 

[Vassago]

I didn't actually realize it posted to the other side, as my computer froze up while posting, oh well.\


Thanks, that's exactly what I was trying to accomplish, but didn't know how to make it look for the " " in the middle of the string.

I'll give this a try, I'm not a amatuer in access, just this one stumped me.

Again, thanks,

Vassago