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

How to get code on search button to do multiple filters

P: 1
I'm new to search forms and i am trying to get a search form to search a subform using a search box called search for which allows the user to type any word or number and the subform results should only show the records with a match. I also have combo boxes called Ministry and Status which will allow the user to select a ministry or status and the subform would only display the records with a match. I also want the user to be able to search by a date range but i also dont know how that is done so havent attempted yet. Please help
Mar 13 '17 #1
Share this Question
Share on Google+
1 Reply


PhilOfWalton
Expert 100+
P: 1,430
My suggestion would be to put all your search fields on the main form. Being a bit short of information, I am going to make the following assumptions:-
1) You have 2 Combo Boxes - CboMinistry & CboStatus that return a number MinistryID and StatusID and that these fields also exist in your subform.

2) You have 2 Text Boxes for Dates FromDate & ToDate and there is a field called MyDate in your subform.

3) There is a Text Box called TxtSearch on the form & there is a field called MyText in your subform.

OK this is air code to build up a filter after setting up as many or few search fields as you want. Obviously you will need to adapt it for your own use, and I stress it is air code

Expand|Select|Wrap|Line Numbers
  1. Private CmdSearch_Click
  2.     Dim Fltr as String
  3.     Dim MinistryFltr as String
  4.     Dim StatusFltr as String
  5.     Dim FromDateFltr as String
  6.     Dim ToDateFltr as String
  7.     Dim TextFltr as String
  8.  
  9.     If Not IsNull(CboMinistry) Then
  10.     MinistryFltr = "MinistryID = " & CboMinistry
  11.     End If
  12.  
  13.     If Not IsNull(CboStatus) Then
  14.     StatusFltr = "StatusID = " & CboStatus
  15.     End If
  16.  
  17.     If Not IsNull(FromDate) Then
  18.     FromDateFltr = "MyDate >= #" & FromDate & "#"
  19.     End If
  20.  
  21.     If Not IsNull(ToDate) Then
  22.     ToDateFltr = "MyDate <= #" & ToDate & "#"
  23.     End If   
  24.  
  25.     If Not IsNull(TxtSearch) Then
  26.     TextFltr = "MyText Like " & Chr$(34) & "*" & TxtSearch & "*" & Chr$(34)
  27.     End If         
  28.  
  29.     If Nz(MinistryFltr) > "" Then
  30.        If Nz(Fltr) > "" Then
  31.        Fltr = Fltr & " AND " & MinistryFltr
  32.        Else
  33.            Fltr = MinistryFltr  
  34.        End If
  35.     End If
  36.  
  37.     If Nz(StatusFltr) > "" Then
  38.        If Nz(Fltr) > "" Then
  39.        Fltr = Fltr & " AND " & StatusFltr
  40.        Else
  41.            Fltr = StatusFltr  
  42.        End If
  43.     End If
  44.  
  45.     If Nz(FromDateFltr) > "" Then
  46.        If Nz(Fltr) > "" Then
  47.        Fltr = Fltr & " AND " & FromDateFltr
  48.        Else
  49.            Fltr = FromDateFltr 
  50.        End If
  51.     End If
  52.  
  53.     If Nz(ToDateFltr) > "" Then
  54.        If Nz(Fltr) > "" Then
  55.        Fltr = Fltr & " AND " & ToDateFltr
  56.        Else
  57.            Fltr = ToDateFltr 
  58.        End If
  59.     End If
  60.  
  61.     If Nz(TextFltr) > "" Then
  62.        If Nz(Fltr) > "" Then
  63.        Fltr = Fltr & " AND " & TextFltr
  64.        Else
  65.            Fltr = TextFltr 
  66.        End If
  67.     End If
  68.  
  69.     If Fltr > "" then
  70.         Me.MySubformName.Form.Filter = Fltr
  71.         Me.MySubformName.Form.FilterOn = True
  72.     Else
  73.         Me.MySubformName.Form.Filter = False
  74.     End If
  75.  
  76. End Sub
  77.  
So I am building a "Mini" Filter for each field and then combining them.

Phil
Mar 14 '17 #2

Post your reply

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