423,473 Members | 2,593 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,473 IT Pros & Developers. It's quick & easy.

MS Access keyword search by multiple fields

P: 5
Hey everyone. Essentially, I'd like to search keywords within a filtered set without resetting my first filter.

I currently have a split form with a combo box containing a dropdown of all fields. Next to it is a text box to search keywords based off the field selected. Next to that is my search button.

The code below allows me to search keywords off any single field. If I do a 2nd search, it searches off the original data set resetting the filter. I would like to search within filtered results. For example, i have 5000 records my first search of customer name "Smith" takes me to 10 records. From those 10, I'd like to select another field from my existing drop down list and keyword search to drill down further.

Here is my code (Text35 is the text box and searchlist is the combobox list of field names):

Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2. Dim strSearchValue As String
  3. strSearchValue = Me.Text35.Value
  4.  
  5. Select Case Me.searchlist.Value
  6.  
  7.         Case "Date"
  8.             Me.Filter = "[Date] = #" & strSearchValue & "# "
  9.  
  10.         Case "Account number"
  11.             Me.Filter = "[Account number] = #' & strSearchValue & '# "
  12.  
  13.         Case "Borrower"
  14.             Me.Filter = "[Borrower] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"
  15.  
  16.         Case "Issue Category"
  17.             Me.Filter = "[Issue Category] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"
  18.  
  19.  
  20. End Select
  21.  
  22. Me.FilterOn = True
  23.  
  24. End Sub
Aug 7 '18 #1

✓ answered by Seth Schrock

There may be an easier way (if any of the other experts want to weight in, I would love to hear it), but what I normally do is check to see if there is a filter currently in place and then if there is, add to the existing filter. So something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2. Dim strSearchValue As String
  3. Dim strFilter As String
  4.  
  5. strSearchValue = Me.Text35   '.value not needed as that is the default property
  6.  
  7. Select Case Me.searchlist.Value
  8.  
  9. Case "Date"
  10. strFilter = "[Date] = #" & strSearchValue & "# "
  11.  
  12. Case "Account number"
  13. strFilter = "[Account number] = #' & strSearchValue & '# "
  14.  
  15. Case "Borrower"
  16. strFilter = "[Borrower] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"
  17.  
  18. Case "Issue Category"
  19. strFilter = "[Issue Category] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"
  20.  
  21.  
  22. End Select
  23.  
  24. If Me.Filter <> "" And Me.FilterOn = True Then
  25.     Me.Filter = Me.Filter & " And " & strFilter
  26. Else
  27.     Me.Filter = strFilter
  28. End If
  29.  
  30. Me.FilterOn = True
  31.  
  32. End Sub

Share this Question
Share on Google+
10 Replies


Seth Schrock
Expert 2.5K+
P: 2,898
There may be an easier way (if any of the other experts want to weight in, I would love to hear it), but what I normally do is check to see if there is a filter currently in place and then if there is, add to the existing filter. So something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Search_Click()
  2. Dim strSearchValue As String
  3. Dim strFilter As String
  4.  
  5. strSearchValue = Me.Text35   '.value not needed as that is the default property
  6.  
  7. Select Case Me.searchlist.Value
  8.  
  9. Case "Date"
  10. strFilter = "[Date] = #" & strSearchValue & "# "
  11.  
  12. Case "Account number"
  13. strFilter = "[Account number] = #' & strSearchValue & '# "
  14.  
  15. Case "Borrower"
  16. strFilter = "[Borrower] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"
  17.  
  18. Case "Issue Category"
  19. strFilter = "[Issue Category] LIKE '*" & (Replace(strSearchValue, "'", "''")) & "*'"
  20.  
  21.  
  22. End Select
  23.  
  24. If Me.Filter <> "" And Me.FilterOn = True Then
  25.     Me.Filter = Me.Filter & " And " & strFilter
  26. Else
  27.     Me.Filter = strFilter
  28. End If
  29.  
  30. Me.FilterOn = True
  31.  
  32. End Sub
Aug 7 '18 #2

twinnyfo
Expert Mod 2.5K+
P: 2,605
Seth,

I like how you think. I was going to suggest along the same lines.

In essence, we are "filtering a filter".

Just don't try the gefilte fish.
Aug 7 '18 #3

P: 5
Wow it worked perfectly. Thank you so much! I have been banging my head rewriting this code for a couple days without finding any help through google or on stack overflow. You come along knowing exactly what I want and solve my issue within a couple minutes.

So by using creating another variable strFilter was essentially creating a 2nd filter for us to compare with Me.Filter?

I don't want to take too much of your time, but I had one other minor question if you didn't mind. When I choose the field "Date" in my drop down list, is it possible to search by an entire month such as March instead of a specific date like 3/15/18?
Aug 7 '18 #4

PhilOfWalton
Expert 100+
P: 1,353
Seth's code looks pretty good except for the line

Expand|Select|Wrap|Line Numbers
  1. strFilter = "[Account number] = #' & strSearchValue & '# "
  2.  
That is assuming the Account Number is a date, which I doubt.

Personally, I hate quotes & Double Quotes and use Chr$(34) instead of Double Quotes. And while I am on pet hates, avoid using spaces in Tables, Queries, Forms & Report Names. Also in Field & Control Names
"AccountNumber" is just as readable as [Account number] and you don't need the square brackets.


I think that line should read
Expand|Select|Wrap|Line Numbers
  1. strFilter = "[Account number] = " & Chr$(34) & strSearchValue & Chr$(34)
  2.  
Is the AccountNumber Text or Numeric?

Phil
Aug 7 '18 #5

twinnyfo
Expert Mod 2.5K+
P: 2,605
DJ2904:
I don't want to take too much of your time, but I had one other minor question if you didn't mind. When I choose the field "Date" in my drop down list, is it possible to search by an entire month such as March instead of a specific date like 3/15/18?
DJ,

Sometimes, we allow a follow-on question within the same thread, if the nature of the question is clearly along the same lines as the initial post. However, based upon your question, I think it best to begin a new thread, as the nature of the second quesiton is quite different (and the solution will be quite different as well).

If you don't mind starting a new thread with that question, and you can always refer back to this original post if you wish.

Thanks!
Aug 7 '18 #6

P: 5
Thanks Phil. That was actually my code mistakenly left in there with the # symbol. I am pretty much a VBA noob, but slowly trying to make sense of it, especially on when to use SQL string and when not to. I strictly use SQL for my current job with other platforms outside of Access, which further explains my rustiness.

I completely agree with you on the spaces in field names. When I work out of SAS, I never use spaces for my field names. Unfortunately I inherited this database from someone else.
Aug 7 '18 #7

twinnyfo
Expert Mod 2.5K+
P: 2,605
I've been in your shoes inheriting DBs..... I always recommend at your first opportunity try to break it down and/or re-create it from scratch--if you have the time and resources (and skill and patience).

The initial headache of taking something apart is worth understanding what's under the hood AND getting it made right.

I know Phil has a few tools up his sleeve to help you take a look at the innards of your beast, if you are so inclined.
Aug 7 '18 #8

P: 5
Yeah I completely understand and almost recreated this one from scratch. One thing I've learned about creating databases for people is that they will never be satisfied. It's always,"Oh, you did a really good job on creating exactly what I wanted. Since you did that part so well, maybe you can also do this other thing...". I guess it keeps me busy at least.

What might these tools be? I'm always up for learning something new.
Aug 7 '18 #9

zmbd
Expert Mod 5K+
P: 5,283
While we're at it:
IMHO: Split forms are almost as evil as lookup fields at the table level.
I cannot count the number of threads I have read here at Bytes and on other forums regarding the issues these forms have caused.
Aug 7 '18 #10

P: 5
I did notice little weird inconsitencies with the split forms, as well as it running slower than a typical form. I tried my best to have one of the managers eliminate the split forms because her employees kept deleting and filtering records messing up the autonumbering. This happened before me so it's all locked down now, but even convincing them to make records uneditable was a chore.

Some people are just set in their ways because it's what they are comfortable with unfortunately. Looks like I'm stuck with these forms for now at least. I'm just glad that I'm only doing this on the side for their team.
Aug 8 '18 #11

Post your reply

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