Search/Filter box that filters across multiple fields based on multiple criteria

[cnstarz]

Hi! I'm basically emulating a split form by having a subform in datasheet view that displays all the records of a table. Clicking on a record in the subform populates the mainform with the record's data, just like in a split form.

Anywho, I have a search/filter box in the header of my main form that filters the subform on the fly. It works great so far, but we want to take it a step further by filtering for records that contain criteria across multiple fields. For instance, assuming the table below:

  ID  | Field A | Field B | Field C 
  1     Cat       Dog       Bird
  2     Earth     Rock      Fire
  3     Rain      Wind      Water
  4     AA Cat    BB Fire   CC Rock
 

Typing in "r" will result in all of the records being there:

  ID  | Field A | Field B | Field C 
  1     Cat       Dog       Bird
  2     Earth     Rock      Fire
  3     Rain      Wind      Water
  4     AA Cat    BB Fire   CC Rock
 

Typing in "ro" will result in:

  ID  | Field A | Field B | Field C 
  2     Earth     Rock      Fire
  4     AA Cat    BB Fire   CC Rock
 

Typing in "rock, fire" will result in:

  ID  | Field A | Field B | Field C 
  2     Earth     Rock      Fire
  4     AA Cat    BB Fire   CC Rock
 

Typing in "rock, fire, a cat" will result in:

  ID  | Field A | Field B | Field C 
  4     AA Cat    BB Fire   CC Rock
 

Typing in "rock, fire, b cat" will result in empty results since no field contains "b cat".

  ID  | Field A | Field B | Field C 

So, commas would separate criteria and only records that contain all criteria would be displayed. This is what we have for the On Change event for our text box:

Private Sub txtFilter_Change()
 
    Dim strFilterText As String
 
    strFilterText = txtFilter.Text
    txtHiddenFilter.Value = strFilterText
 
  Me.sbfm_Tasks.Form.Filter = "TaskName Like '*" & strFilterText & "*' Or TaskDescription Like '*" & strFilterText & "*'"
  Me.sbfm_Tasks.Form.FilterOn = True
 
 
'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box txtFilter
    If Len(Me.txtHiddenFilter) <> 0 And InStr(Len(txtHiddenFilter), txtHiddenFilter, " ", vbTextCompare) Then
        'Returns the cursor to the the end of the text in Text Box txtFilter,
        'and restores trailing space lost when focus is shifted to the list box
            Me.txtFilter = strFilterText
            Me.txtFilter.SetFocus
            Me.txtFilter.SelStart = Me.txtFilter.SelLength
            Exit Sub
    End If
 
'Returns the cursor to the the end of the text in Text Box txtFilter
    Me.txtFilter.SetFocus
    If Not IsNull(Len(strFilterText)) Then
        Me.txtFilter.SelStart = Len(strFilterText)
    End If
 
End Sub

How can this code be modified to satisfy our request? Thanks!

[cnstarz]

Here's a zipped copy of my database in accdb and mdb format.

[Seth Schrock]

It isn't the easiest of things to do, but it is possible and like most things, it can be done multiple ways. Here is how I would do it. First, it seems like you are wanting multiple criteria that would be separated by a comma. So, it would probably be simplest to use the Split function with the comma as the delimiter. Next, you need to compare your all your criteria to all the fields. Probably the easiest way would be to loop through your criteria inside a loop of the fields. Here is what you will end up with.

SELECT * FROM MyTable
WHERE [Field A] Like '*rock*' 
        OR [Field A] Like '*fire*' 
        OR [Field A] Like '*a cat*'
        OR [Field B] Like '*rock*' 
        OR [Field B] Like '*fire*' 
        OR [Field B] Like '*a cat*'
        OR [Field C] Like '*rock*' 
        OR [Field C] Like '*fire*' 
        OR [Field C] Like '*a cat*'

I can't help but think that there will be a performance problem though depending on how many criteria you put in and how many fields you need the criteria applied to.

[GregHB]

Wow! I feel like a light-bulb just got turned on. I had done a similar query for a knowledgebase that I developed last summer, but I had used the [Field A] or [Field B] or [Field C] approach, similar to what Seth wrote. However, I had not even thought of trying what he wrote.

Using that type of approach may help add a lot of additional functionality for my team / customers (~50 IT Trainers).

I wish I could contribute more to your cause, CNStarz, but here's hoping that this thread can help us both out. Thanks for the question, I hadn't even thought how to phrase it.

[twinnyfo]

cnstarz,

An alternate method would be for the Change Event to first evaluate mow many criteria have been entered (i.e. to perform a character-by-character assessment, looking for a comma to delineate).

Then, the code could Dim an array, with that many cells. Then, using language similar to Seth's cycle through the search criteria and build a very complex WHERE clause. Depending on whether you wanted inclusive or exclusive (whether it is any records that contain any of the criteria versus any records that contain at least some of the criteria) this could get very complicated.

It could be done, and as Seth mentions, there are probably a multitude of ways to do it. Either way, you have to build in a method to assess all the search criteria first, then build your WHERE clause around that.

Hope this hepps.