Results 1 to 2 of 2
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Edinburgh, Midlothian, Scotland
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Multiple Filters (97)

    Hi folks,

    I've inherited (groan!) a database from an employee that has just left our company and am struggling to get the filters to work.

    It has a simple form, with displayed data on the right and two (soon to be more) combo boxes on the left. The combo boxes allow users to select citeria to filter by (specifically username and company).

    Rather than have the combo boxes filter on their "after update" events, I would like a user to be able to select the criteria from the two boxes and then click an "Apply Filter" button which runs the filter based on the selections made.

    I can easily do it for one combo box criteria using the "on click" button event to run the code DoCmd.ApplyFilter , "[Auto_ID] = Me!Combo25.Value".

    My question is, therefore, how can I stack up these filters,so that the filter runs on the values of two (or more) combo boxes? I guess its an SQL thing?

    Also, I would like the multiple filter to accept values where a combo box hasn't be used - i.e. user may want to specify filter criteria in CB's 1, 3 and 4, but leaves 2 and 5 untouched??

    Finally! How can I stop the whole form going blank when the filter doesn't find any values?

    Phew!

    Thanks in advance,

    Ade

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Multiple Filters (97)

    This sample code is doing such selection to build the sql string for a report based on the different combo's from a form named "frmprintselecties".
    With some adjustments you can use it for your application.

    <pre>Private Sub Report_Open(Cancel As Integer)
    Dim strSQL As String
    Dim ysnCond As Boolean
    ysnCond = False
    If IsLoaded("frmprintselecties") Then
    If "" & Forms!frmprintselecties!cmbActiviteit <> "" Then
    strSQL = "SELECT * from qryKlantActivAll WHERE IDActiviteit = " _
    & Forms!frmprintselecties!cmbActiviteit & " "
    ysnCond = True
    Else
    strSQL = "SELECT * from qryKlantAlfa "
    ysnCond = False
    End If
    If "" & Forms!frmprintselecties!cmbverteg <> "" Then
    If ysnCond Then
    strSQL = strSQL & "AND "
    Else
    strSQL = strSQL & "WHERE "
    End If
    strSQL = strSQL & "lngvertegenw = " & _
    Forms!frmprintselecties!cmbverteg
    ysnCond = True
    End If
    If "" & Forms!frmprintselecties!cmbtelefoon <> "" Then
    If ysnCond Then
    strSQL = strSQL & "AND "
    Else
    strSQL = strSQL & " WHERE "
    End If
    strSQL = strSQL & "txtTelefoon LIKE " & Chr(34) & _
    Forms!frmprintselecties!cmbtelefoon & "*" & Chr(34)
    ysnCond = True

    End If
    If "" & Forms!frmprintselecties!cmbLandcode <> "" Then
    If ysnCond Then
    strSQL = strSQL & "AND "
    Else
    strSQL = strSQL & " WHERE "
    End If
    strSQL = strSQL & "txtlandcode = " & _
    Chr(34) & Forms!frmprintselecties!cmbLandcode & Chr(34)
    ysnCond = True
    End If
    If "" & Forms!frmprintselecties!cmbTaal <> "" Then
    If ysnCond Then
    strSQL = strSQL & "AND "
    Else
    strSQL = strSQL & " WHERE "
    End If
    strSQL = strSQL & "txttaal = " & Chr(34) & _
    Forms!frmprintselecties!cmbTaal & Chr(34)
    ysnCond = True
    End If
    If "" & Forms!frmprintselecties!cmbDocum = "Ja" Then
    If ysnCond Then
    strSQL = strSQL & "AND "
    Else
    strSQL = strSQL & " WHERE "
    End If
    strSQL = strSQL & "ysndocum = " & Forms!frmprintselecties!ysnDocum
    ysnCond = True
    End If

    Me.RecordSource = strSQL
    Else
    MsgBox "This repport can only been " & _
    "printed when calling from Printing Selections Form", , "Error"
    Cancel = True
    End If

    End Sub</pre>

    Succes
    Francois

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •