Multiple Filter on a form

[Aliffi]

Dear All , Specially Paul
i have 4 or 5 text boxes on top of my form

now i can do filter with one text box and a button
when i click the button it matches the value in the box and a filed on the form , now i want to have 5 boxes which i write the values and it match all of them with fields . on the form . and when i press the button it could filter up on all ofthe 5 boxes criteria

the boxes will be optional to enter value or not
some time may be 4 some times may be 2 ,
for entry others may be empty

regards

 

[willir]

Aliffi

Use the text or combo boxes to build your WHERE clause.

for example...

Dim strSQL as String, strQ as String, strWhere as String

strQ = Chr$(34)
strWhere = ""

strSQL = "SELECT * From YourTable"

If Len(Nz(Me.TextBox, "")) > 0 Then
   strWhere = " Where YourTextField = " & strQ & Me.TextBox & strQ
End If

If IsDate(Me.DateBox) Then
   If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND YourDateField = #" & Me.DateBox & "#")
   Else
      strWhere = " Where YourDateField = #" & Me.DateBox & "#")
   End If
End If

If Nz(Me.NumberBox, 0) > 0 Then

   If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND YourNumberField = " & Me.NumberBox
   Else
      strWhere = " Where YourNumberField = " & Me.NumberBox
   End If
End If

strSQL = strSQL & strWhere

Me.RecordSource = strSQL
Me.Requery

This is just an overview, but hopefully it gives you the idea.

Richard

 

[Aliffi]

Dear Richard , the method you given me is for requering the combo boxes, but i want simple text boxes on top of my form
when i press the button , it should match al the values with the existing fields on form and then ,
filter them up.

the probelm is when i filter with one box it removes the other filter ....i want all of them at the same time.



regards

 

[willir]

Actual, the code I gave you requries the form based on any of the selected fields. Same idea as cascading combo boxes, but applied to the form.

Note the ending code...
Me.RecordSource = strSQL
Me.Requery

This code is for the form, not a combo box.

 

[Aliffi]

=========================================================
Province[________]
Agency [________]
sector [________]

[submit button]
=========================================================
[ID#] [Project Title] [ Province] [Agency][sector]
---------------------------------------------------------
0526 Food Security Harir HBF Food

in the above example i want all my code to be set on bhind the submit button after i enter the criteria on the texboxes above i press the button and it filter up data on the current form as it shows below...

regards

 

[willir]

Aliffi

I still do not know the name of your table, and you may want each of the controls to be combo boxes so the end user can select the item(s) they want.

Assumptions...
- text / combo boxes use the names presented (Provence, Agency, sector). Each is a data type test.
- tblProject - name of table
- SubMit - name of command button

Code behind the SubMit command button.

Private Sub SubMit_OnClick()

Dim strSQL as String, strQ as String, strWhere as String

strSQL = "SELECT * FROM tblProject"
strQ = Chr$(34)  ' Double quote symbol
strWhere = ""

If Len(Nz(Me.Provence, "")) Then
   strWhere = " WHERE Provence = " & strQ & Me.Provence & strQ
End If

If Len(Nz(Me.Agency, "")) Then
   If Len(strWhere) Then
      strWhere = strWhere & " AND Agency = " & strQ & Me.Agency & strQ
   Else
      strWhere = " WHERE Agency = " & strQ & Me.Agency & strQ
   End If
End If

If Len(Nz(Me.Sector, "")) Then
   If Len(strWhere) Then
      strWhere = strWhere & " AND Sector = " & strQ & Me.Sector & strQ
   Else
      strWhere = " WHERE Sector = " & strQ & Me.Sector & strQ
   End If
End If

Me.RecordSource = strSQL
Me.Requery

You will have to ensure the name of the tables, fields and controls are correct on your end.

If the above was on a main form, and you want to display the results in a subform, then code is changed just slightly at the end...

Me.YourSubform.Form.RecordSource = strSQL
Me.YourSubform.Requery

For performance, it is better to build the WHERE clause in order of the way the fields are indexed. Specifically, if you have a large database, you may see an improvement in performance by indexing provence, agency and sector.

Richard