Multiple filter

[Aliffi]

Dear All,
i have a from , with some 2 text boxes at the header of ther form, and i have a button this code below is behind the button when i press the button it should filter the data matchin the values from text boxes and thoes fields on the form.
NOW, the problem is that it do filter but one by one
i want to combine these two sentences with OR or AND i dont kwo in to one statment where when i click the button it shoud do multiple filter.

regards

Private Sub Command118_Click()

DoCmd.ApplyFilter , "province = '" & Forms!filter_form!prof & "'"
DoCmd.ApplyFilter , "name_of_agency = '" & Forms!filter_form!agenf & "'"

End Sub

 

[VBslammer]

Private Sub Command118_Click()
  Dim strWhere As String
  Dim frm As Form

  Set frm = Forms!filter_form.Form
  
  If Nz(frm!prof, "") <> "" Then
    strWhere = "province='" & frm!prof & "'"
  End If

  If Nz(frm!agenf, "") <> "" Then
    If Len(strWhere) > 0 Then
      strWhere = strWhere & " AND name_of_agency='" & frm!agenf & "'"
    Else
      strWhere = "name_of_agency='" & frm!agenf & "'"
    End If
  End If

  If Len(strWhere) > 0 Then
    DoCmd.ApplyFilter , strWhere
  Else
    MsgBox "Please enter criteria", vbExclamation, "Alert"
  End If
End Sub

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Aliffi]

VB Slammer , you are Greate......

A bunch of thanks , u solved a big problem for me

thank you very much again

regards

farzam

 

[Aliffi]

sir How if I have 4 more boxes , total six boxes

how can i compare all of them

PLZ HELP
Thanks in advance


Private Sub Command118_Click()

DoCmd.ApplyFilter , "province = '" & Forms!filter_form!prof & "'"
DoCmd.ApplyFilter , "name_of_agency = '" & Forms!filter_form!agenf & "'"

DoCmd.ApplyFilter , "year = '" & Forms!filter_form!yeaf & "'"

DoCmd.ApplyFilter , "task_manager = '" & Forms!filter_form!taskmf & "'"

DoCmd.ApplyFilter , "Main_sector = '" & Forms!filter_form!msf & "'"

DoCmd.ApplyFilter , "district = '" & Forms!filter_form!disf & "'"

End Sub

 

[VBslammer]

If you have that many fields to process, I would move the filter building logic into a separate function. Something like this would be easier to read and modify later:

Private Sub Command118_Click()
  Dim strWhere As String
  Dim frm As Form

  Set frm = Forms!filter_form.Form
  
  strWhere = AppendWhere(frm!prof, "province='" + frm!prof + "'", strWhere)
  strWhere = AppendWhere(frm!agenf, "name_of_agency='" + frm!agenf + "'", strWhere)
  strWhere = AppendWhere(frm!yeaf, "year='" + frm!yeaf + "'", strWhere)
  strWhere = AppendWhere(frm!taskmf, "task_manager='" + frm!taskmf + "'", strWhere)
  strWhere = AppendWhere(frm!msf, "Main_sector='" + frm!msf + "'", strWhere)
  strWhere = AppendWhere(frm!disf, "district='" + frm!disf + "'", strWhere)

  If Len(strWhere) > 0 Then
    DoCmd.ApplyFilter , strWhere
  Else
    MsgBox "Please enter criteria", vbExclamation, "Alert"
  End If
End Sub

[green]'@--------------------------------------------------------@[/green]

Function AppendWhere(ByVal ControlValue As Variant, _
                     ByVal ValueToAppend As Variant, _
                     ByVal WhereClause As String) As String
  If Nz(ControlValue, "") = "" Then
    AppendWhere = WhereClause
  Else
    If Len(WhereClause) > 0 Then
      AppendWhere = WhereClause & " AND " & ValueToAppend
    Else
      AppendWhere = ValueToAppend
    End If
  End If
End Function

VBSlammer

"You just have to know which screws to turn." - Professor Bob