Results 1 to 8 of 8
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering form (Access 2000)

    I need to filter a form per 2 criteria : per month and per office.How could I do it with both of them ?
    The record source of the form is :
    SELECT TblClients.ClientID, CallsClients.CallDate, TblClients.afid
    FROM TblClients INNER JOIN CallsClients ON TblClients.ClientID = CallsClients.ClientID
    ORDER BY CallsClients.CallDate;

    I have also 2 option groups.The first option group is called Monaten with 12 options for the months. The second option group is called office with 10 options. The first option is afid = 1, the second is afid = 2 etc.

    The controls on the forms in a simplified mode are :
    Clientid, call date, afid

  2. #2
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering form (Access 2000)

    Just to let you know that your code works so fine ! Thank you so much !

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering form (Access 2000)

    Place a command button cmdFilter on the form.
    Create the following On Click event procedure for the command button:

    Private Sub cmdFilter_Click()
    Dim strFilter As String

    If Not IsNull(Me.Monaten) Then
    strFilter = " AND Month(CallDate) = " & Me.Monaten
    End If

    If Not IsNull(Me.Office) Then
    strFilter = strFilter & " AND afid = " & Me.Office
    End If

    If Not strFilter = "" Then
    ' Remove first " AND "
    strFilter = Mid(strFilter, 6)
    Me.Filter = strFilter
    Me.FilterOn = True
    Else
    Me.FilterOn = False
    End If
    End Sub

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering form (Access 2000)

    Dear Hans


    I have extended my query to include 2 additional criteria Reminder and Earmark . After the filtering, which works fine, is it possible to add on additional comand buttons for filtering when Reminder Is Not Null and also when EarMark is True ?
    StrSQL = " SELECT TblClients.ClientID, TblClients.CompanyName, TblClients.address, TblClients.City, CallsClients.CallID, CallsClients.CallDate, CallsClients.Notes, CallsClients.Subject, TblClients.afid, TblClients.Reminder,TblClients.EarMark" & _
    " FROM TblClients INNER JOIN CallsClients ON TblClients.ClientID = CallsClients.ClientID " & _
    " WHERE (((CallsClients.CallDate) > #1/1/2008#))" & _
    " ORDER BY CallsClients.CallDate DESC"

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering form (Access 2000)

    Do you want these command buttons to operate independently of the others, or should they take a filter set by another command button into account?

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering form (Access 2000)

    So it is possible? Could i have it both ? I mean to drill down the filter set by the previous filtering, which filters per office and per month, so to filter within this set, and secondly to filter independently of them ? But these two controls Reminder and Earmark are not required to be interdendant.Filtering will be either with reminder or with earmark, but not together.
    Thank you

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Filtering form (Access 2000)

    For filtering on Reminder:
    <code>
    Private Sub cmdFilter2_Click()
    Dim strFilter As String

    strFilter = "Reminder Is Not Null"

    If Not IsNull(Me.Monaten) Then
    strFilter = strFilter & " AND Month(CallDate) = " & Me.Monaten
    End If

    If Not IsNull(Me.Office) Then
    strFilter = strFilter & " AND afid = " & Me.Office
    End If

    Me.Filter = strFilter
    Me.FilterOn = True
    End Sub
    </code>
    and for filtering on Earmark:
    <code>
    Private Sub cmdFilter3_Click()
    Dim strFilter As String

    strFilter = "Earmark = True"

    If Not IsNull(Me.Monaten) Then
    strFilter = strFilter & " AND Month(CallDate) = " & Me.Monaten
    End If

    If Not IsNull(Me.Office) Then
    strFilter = strFilter & " AND afid = " & Me.Office
    End If

    Me.Filter = strFilter
    Me.FilterOn = True
    End Sub</code>

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Posts
    537
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering form (Access 2000)

    Thank you Hans for the wonderfl suggestion.It goes without saying that all works perfect and i thank you very much.
    I wish you a happy weekend

Posting Permissions

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