473,326 Members | 2,136 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to get code on search button to do multiple filters

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
1 1055
PhilOfWalton
1,430 Expert 1GB
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

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

Similar topics

3
by: Bryan Harrington | last post by:
Hello all.. I'm working on an application that has a worklist of items for users to work on. I'd like to, based on user security, to filter a worklist based on between 1 and 8 items (i.e.,...
8
by: Steph | last post by:
Hi. I'm very new to MS Access and have been presented with an Access database of contacts by my employer. I am trying to redesign the main form of the database so that a button entitled...
5
by: JP SIngh | last post by:
Hi All This is a complicated one, not for the faint hearted :) :) :) Please help if you can how to achieve this search. We have a freetext search entry box to allow users to search the...
1
by: tagnum | last post by:
I noticed a strange behaviour in IE 6. Not sure whether it's a bug in my code or because of IE 6. I have written a search function using a standard search input box and search button. (HTML input...
2
by: Pamr | last post by:
My site is programmed with php and javascript. I have a search button which it searches records from my database. I have two different programming codes into the same file which retrieve data, the...
3
by: hessah | last post by:
I am a beginner with Microsoft Access databases. Is is possible someone could help me with how I would place VB code behind a search button? Im developing database, i want to add Search button to...
9
by: NvyAccessUser | last post by:
Dear Sir I've a table called "Table3" with primary key and questions -------------------- P.K | Question -------------------- I'd like to search in the questions field using multiple...
2
by: dannyboy7 | last post by:
I have created a form with combo box and a search button to search for a book title entered in the combo box from the books table and display the other details of the book in a book subform.Pls I...
1
by: mdshakilahmmed | last post by:
i have a simple access from where i am create simple search button to find record. search button works good, here is may question is,i want to create search button where i am just click the button &...
0
by: cleg7700 | last post by:
Hello, I am trying to search from 3 list boxes each of which are multiselect. The source is a single table (at this point). I currently have a form with the list boxes and search button that will...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.