how: search dynamically using mult search filter terms [based on john big booty's db] (1 Viewer)

[Minddumps]

how to use delimiter to search multiple filters [using john big booty's db]

I implemented John Big Booty's dynamic search (found here: http://www.access-programmers.co.uk/forums/showthread.php?t=188663) using Access 2007 a while back and was recently asked by one of my work team members to change it to allow multiple search terms.

I was first thinking to add in the query

Like "*" & [Forms]![frmSearch]![txtSearch2] & "*" OR Like "*" & [Forms]![frmSearch]![txtSearch3] & "*"

and add those fields in the form as well, but this seems so tedious and then to have too many search boxes on the form would look ugly and probably slow down the process of the page, especially since I don't want to put a cap on the amount of search terms.

So does anyone have a better idea? or some code so I can separate search terms in the same search field on the form? ex. TN|KY|FL|WO|OK So any records matching any of those filters will show up in the form...??

 

[James Dudden]

You could use the split function and then put the contents into an array. As long as you use a pre-determined character to define when there is a split then you should be fine. Something like this should work:

Dim strArray() As String, strSplit As String, intCount As Integer
strArray = Split(Me!TestBox, "|")
For intCount = LBound(strArray) To UBound(strArray)
If strSplit <> "" Then strSplit = strSplit & " OR "
strSplit = strSplit & "Like *" & strArray(intCount) & "*"
Next

 

[Minddumps]

Thanks for your response! Hmm please forgive my ignorance though, I'm playing around with what you suggested and am still not getting anywhere.

I added what you provided to the on change event of the hidden textbox the query views in order to look up the search terms: txtSearch2. I also changed the Me!Testbox you provided to the name of the main txtSearch.

Per John's db, that main text box has this:

Private Sub txtSearch_Change()
Dim vSearchString As String
    vSearchString = txtSearch.Text
    txtSearch2.Value = vSearchString
    Me.List30.Requery
   
End Sub

No errors show, but whenever I add the delimiter, no results are found because it's not splitting the terms separately (it's treating the delimiter as part of the search).

I've never worked with split functions and have no clue what to do... any idea what I may be doing wrong?

 

[James Dudden]

Sorry, I hadn't actually looked at the dynamic search demo you have based yours on so my solution may not help. Leave it with me and I will download the demo and then get back to you.

 

[James Dudden]

Hi, the only way I can see this working is to remove all the hard-written filters in the query and then dynamically create the rowsource for the list box. So, you would still use my idea to split the string and then use that to build a where statement.

I don't know if this would work smoothly in a dynamic way as the screen currently does but it would definitely work if you wanted the user to input all search terms and then press enter.

Try something like this:
(but first do a backup and then remove the hard written filters in the query)

    SrchText.Value = vSearchString
  
    Dim strArray() As String, strSplit As String, intCount As Integer
    strArray = Split(vSearchString, "|")
    For intCount = LBound(strArray) To UBound(strArray)
        If strSplit <> "" Then strSplit = strSplit & " OR "        
         strSplit =  strSplit & "Grape Like '*" & strArray(intCount) & "*' OR "
        strSplit = strSplit & "Winery Like '*" & strArray(intCount) & "*' OR "
        strSplit = strSplit & "Region Like '*" & strArray(intCount) & "*' OR "
        strSplit = strSplit & "Wine Like '*" & strArray(intCount) & "*'"
    Next
    
     strTemp = "SELECT ..." ADD THE SQL HERE WITH NO WHERE STATEMENT AS THAT WILL GET ADDED NEXT
  
     Me.SearchResults.RowSource = strTemp & " WHERE " & strSplit & ";"

 

[Minddumps]

Phew, I think my brain is just dead. I'm still not getting any where. Thank you again for your help. I'll try again on Monday and write back. Have a wonderful weekend.

 

[CJ_London]

this link may help

http://www.access-programmers.co.uk/forums/showthread.php?t=260589