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

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

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1.   ID  | Field A | Field B | Field C 
  2.   1     Cat       Dog       Bird
  3.   2     Earth     Rock      Fire
  4.   3     Rain      Wind      Water
  5.   4     AA Cat    BB Fire   CC Rock
  6.  
Typing in "r" will result in all of the records being there:

Expand|Select|Wrap|Line Numbers
  1.   ID  | Field A | Field B | Field C 
  2.   1     Cat       Dog       Bird
  3.   2     Earth     Rock      Fire
  4.   3     Rain      Wind      Water
  5.   4     AA Cat    BB Fire   CC Rock
  6.  
Typing in "ro" will result in:

Expand|Select|Wrap|Line Numbers
  1.   ID  | Field A | Field B | Field C 
  2.   2     Earth     Rock      Fire
  3.   4     AA Cat    BB Fire   CC Rock
  4.  
Typing in "rock, fire" will result in:

Expand|Select|Wrap|Line Numbers
  1.   ID  | Field A | Field B | Field C 
  2.   2     Earth     Rock      Fire
  3.   4     AA Cat    BB Fire   CC Rock
  4.  
Typing in "rock, fire, a cat" will result in:

Expand|Select|Wrap|Line Numbers
  1.   ID  | Field A | Field B | Field C 
  2.   4     AA Cat    BB Fire   CC Rock
  3.  

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

Expand|Select|Wrap|Line Numbers
  1.   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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub txtFilter_Change()
  2.  
  3.     Dim strFilterText As String
  4.  
  5.     strFilterText = txtFilter.Text
  6.     txtHiddenFilter.Value = strFilterText
  7.  
  8.   Me.sbfm_Tasks.Form.Filter = "TaskName Like '*" & strFilterText & "*' Or TaskDescription Like '*" & strFilterText & "*'"
  9.   Me.sbfm_Tasks.Form.FilterOn = True
  10.  
  11.  
  12. 'Tests for a trailing space and exits the sub routine at this point
  13. 'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box txtFilter
  14.     If Len(Me.txtHiddenFilter) <> 0 And InStr(Len(txtHiddenFilter), txtHiddenFilter, " ", vbTextCompare) Then
  15.         'Returns the cursor to the the end of the text in Text Box txtFilter,
  16.         'and restores trailing space lost when focus is shifted to the list box
  17.             Me.txtFilter = strFilterText
  18.             Me.txtFilter.SetFocus
  19.             Me.txtFilter.SelStart = Me.txtFilter.SelLength
  20.             Exit Sub
  21.     End If
  22.  
  23. 'Returns the cursor to the the end of the text in Text Box txtFilter
  24.     Me.txtFilter.SetFocus
  25.     If Not IsNull(Len(strFilterText)) Then
  26.         Me.txtFilter.SelStart = Len(strFilterText)
  27.     End If
  28.  
  29. End Sub
How can this code be modified to satisfy our request? Thanks!
Jun 10 '14 #1
Share this Question
Share on Google+
4 Replies


P: 2
Here's a zipped copy of my database in accdb and mdb format.
Attached Files
File Type: zip notesreportfixed.zip (128.4 KB, 59 views)
Jun 10 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM MyTable
  2. WHERE [Field A] Like '*rock*' 
  3.         OR [Field A] Like '*fire*' 
  4.         OR [Field A] Like '*a cat*'
  5.         OR [Field B] Like '*rock*' 
  6.         OR [Field B] Like '*fire*' 
  7.         OR [Field B] Like '*a cat*'
  8.         OR [Field C] Like '*rock*' 
  9.         OR [Field C] Like '*fire*' 
  10.         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.
Jun 10 '14 #3

P: 2
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.
Jun 10 '14 #4

twinnyfo
Expert Mod 2.5K+
P: 3,482
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.
Jun 10 '14 #5

Post your reply

Sign in to post your reply or Sign up for a free account.