Results 1 to 13 of 13

Filtering a Report

  1. #1
    musicalogist is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    10

    Filtering a Report

    Okay, so my way-too-hard-for-me access project is ALMOST complete, thanks to many hours of googling and some great help from you guys in this forum. I sincerely thank you

    I will never again put ACCESS on my resume...serves me right for lying…

    I am currently lost with what should be the easiest thing.

    I have a report, based on a query, that is opened through an "open report" button on a form. This form also includes 2 filter criteria: "client" and "date" The appropriate criteria is set up on the query so that when I fill in these blanks, the report opens up nice and filtered.

    However...

    What if I ONLY want to filter by ONE of these criteria, e.g date, leaving the other one blank? And vice versa? or what If I want to leave both blank, and just open the report with no filters? Is there a way to do this?

    I tried having two buttons to open two duplicate reports, one with and one without filters. This is most likely not the most efficient way, but it works. However, it only works with one filter. I need 2, possibly more.

    One more thing. The DATE criteria is entered by specifying a start date and an end date. So if I want to look up the records for March, I'd select the first and the last day of march. Ideally, but not necessary, especially if it's a pain in the ass to code, I would like to have 2 dropdown lists, one with the 12 months and one with a list of years, and filter it that way.

  2. #2
    TheShabz is offline Court Jester
    Windows XP Access 2003
    Join Date
    Feb 2010
    Posts
    1,370
    Look up the VBA "switch" statement. Basically its used to replace long if, else-if, else-if, else statements. You can set up one case to where client="". one to where date="", and one where client="" AND date="". That's a pretty brute force way of doing it though and I'm sure the others here can probably come up with better.

  3. #3
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    927
    There are many ways to filter a report.
    1) If you Criterias are predefined than you can use a Query. You can place the criteria in the underlying Query of your Report.
    2) Then you can open a Report from a Form and the conditions can be derived from Form objects Like text and combo boxes.

    e.g.

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "Table1"

    stLinkCriteria = "[MemberName]=" & "'" & Me![Combo0] & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria

    Here a Report is opened and the MemberName is equal to a ComBox Combo0 on the form.


    You can also set filter on the OnOpen Event of a Report

    E.g.

    Docmd.ApplyFilter, "[MemberName]=" & "'" & Me.Combo0 & "'"

    or
    Me.Filter="[MemberName]=" & "'" & Me.Combo0 & "'"
    Me.Filteron=True


    But What If my conditions are not pre-defined and I want to apply conditions dynamically......

    This is What I have done:

    I have one Table:
    Table1={ID,MemberName,Skill,Designation,Marks}

    No if I at any point of Time apply Filter on a Report Table1 and want to view the data according to:
    1) MemberName
    2) Designation
    3) MemberName and Designation
    4) View all

    I have a Form with Two Check Boxes, two Combo Boxes, and a Command Button.

    When You want to Open a Report:
    By Name: Select Only Check Box Name and Click on Command Button
    By Designation: Select Only Check Box Designation and Click on Command Button
    By Both: Select Both and Click on Command Button
    To view all: just Click the Command Button.

    How does it work:

    Whe you clik the check boxes it creates a Number.
    Checking Name add 1
    Checking Designation adds 2

    So clicking only Name, Number=1
    Clicking Only Designation, Number =2
    Name and Designation=12
    Designation and Name=21

    The Select Case and If is used to create the Condition in order.

    Then I run a For loop and create the strStringCondition.
    strStringCondition is set to ="" because a user will never follow the sequence of selection he may select Name First and then Designation or vice versa to compensate this I have used a if statement.

    The loop scans the number from Left, each Single Number at a time and accordingly the condition is created.

    When multiple conditions are to be include the if statemnt adds the "And to the begining of the second condition.

    Code used:

    Check Boxes:
    Private Sub Check4_AfterUpdate()
    Me.Text11 = Me.Text11 & 1
    Me.Check4.Locked = True
    End Sub

    Private Sub Check6_AfterUpdate()
    Me.Text11 = Me.Text11 & 2
    Me.Check6.Locked = True
    End Sub

    Command Buttons:

    Private Sub Command10_Click()
    On Error GoTo Err_Command10_Click
    '*************************************************
    '* Written by Siddthartha Majumdar on 04/23/2010 *
    '* Scope generate auto condition to filter Report*
    '*************************************************

    Dim strStringCondition As String
    Dim intMyumber As Integer
    Dim intCounter As Integer
    Dim intIndex As Integer
    Dim strDocName As String
    intMynumber = Me.Text11 'Text11 is a Text box on the form whic is Hidden
    strStringCondition = ""
    If IsNull(Me.Text11) Then
    srtstringCondition = ""
    Else
    For intCounter = 1 To Len(intMynumber + 1)
    intIndex = Right$(Left$(intMynumber, intCounter), 1) 'Scanning the number sequence created by the Check boxes

    'Assignment of Conditions to strStringCondition
    Select Case intIndex
    Case Is = 1
    If strStringCondition = "" Then
    strStringCondition = "[MemberName]=" & "'" & Me.Combo0 & "'"
    Else
    strStringCondition = strStringCondition & " And [MemberName]=" & "'" & Me.Combo0 & "'"
    End If
    Case Is = 2
    If strStringCondition = "" Then
    strStringCondition = "[Designation]=" & "'" & Me.Combo13 & "'"
    Else
    strStringCondition = strStringCondition & " And [Designation]=" & "'" & Me.Combo13 & "'"
    End If
    End Select
    Next
    End If
    MsgBox strStringCondition

    DoCmd.OpenReport "Table1", acViewPreview, , strStringCondition
    strStringCondition = ""
    Me.Text11 = Null
    Me.Check4 = False 'All seting are reversed
    Me.Check6 = False
    Me.Check4.Locked = False
    Me.Check6.Locked = False
    Me.Combo0 = ""
    Me.Combo13 = ""

    Exit_Command10_Click:
    Exit Sub

    Err_Command10_Click:
    MsgBox Err.Description
    Resume Exit_Command10_Click

    End Sub

    Please refer to the attached mdb.

    I have made some sample entries:

    Member Name=RuralGuy
    Designation=SuperModerator

    Member Name=Pbaldy
    Designation=Who is John Galt

    Member Name = Maximus
    Designation=<= Novice

    if this solves your problem mark this thread solved.
    Last edited by maximus; 04-23-2010 at 02:53 AM.
    Note: Please change the names of the tables, queries, form, controls in the example with the ones in your database

    How to Mark a thread solved:
    http://www.accessforums.net/forum-su...lved-1828.html

    Contact
    for Solutions to your database problems and at affordable rates
    silverback_bats@yahoo.co.in / m.exodus2@gmail.com

  4. #4
    Datagopherdan is offline Competent Performer
    Windows 7 Access 2007
    Join Date
    Dec 2008
    Posts
    220
    Another alternative you may want to use is setting the criteria at the form level. I'm very much against using parameter queries. The reason being is that by doing that, you're committing that one query where it can only be used with one report or form. By writing a procedure, you're not locking that query, so the same query can be used in different areas. Below is the code that you can try, which will compensate for if the user selects one, both or no selections for filtering. Note, this code is based on your Client field being a numeric value. Ideally, you should have a unique identifier for the client such as an auto-id field.

    For the date fields, you would set up 2 unbound text boxes (txtBegin and txtEnd), so the user can enter a range of dates.

    Code:
    Private Sub cmdReport_Click()
    Dim strClient As String
    Dim strDateRange As String
    Dim strSQL As String
    'Clear the variable
    strSQL = ""
    'Perform validations
    If IsNull(Me.txtBegin) Then
        If Not IsNull(Me.txtEnd) Then
            MsgBox "Most date fields must be completed or left blank"
                Exit Sub
        End If
    End If
    If Not IsNull(Me.txtBegin) Then
        If IsNull(Me.txtEnd) Then
            MsgBox "Most date fields must be completed or left blank"
                Exit Sub
        End If
    End If
    If Not IsNull(Me.txtBegin) Then
        If Not IsNull(Me.txtEnd) Then
            If DateDiff("d", Me.txtBegin, Me.txtEnd) < 0 Then
                MsgBox "From date must be prior to or equal to the To date"
                    Exit Sub
            End If
        End If
    End If
    If Not IsNull(Me.txtBegin) Then
        strDateRange = "[TheDate] Between #" & Me.txtBegin & "# and #" & Me.txtEnd & "#"
        If Len(strSQL) > 0 Then
            strSQL = strSQL & " and " & strDateRange
        Else
            strSQL = strDateRange
        End If
    End If
    If Not IsNull(Me.Client) Then
        strClient = "[Client]=" & Me.Client
            If Len(strSQL) > 0 Then
                strSQL = strSQL & " and " & strClient
            Else
                strSQL = strClient
        End If
    End If
     
    If Len(strSQL) > 0 Then 'Report is filtered
        DoCmd.OpenReport "MyReport", acViewPreview, , strSQL
    Else 'No filter
        DoCmd.OpenReport "MyReport", acViewPreview
    End If
     
     
    End Sub

  5. #5
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    927
    Wonderfuly done sir
    Note: Please change the names of the tables, queries, form, controls in the example with the ones in your database

    How to Mark a thread solved:
    http://www.accessforums.net/forum-su...lved-1828.html

    Contact
    for Solutions to your database problems and at affordable rates
    silverback_bats@yahoo.co.in / m.exodus2@gmail.com

  6. #6
    musicalogist is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    10

    looks good

    Thank you!

    I tried the code, and the filtering by client works perfectly. However, the date filter behaves very erratically. Sometimes it doesn't apply any filter, sometimes it does it perfectly, sometimes it filters it in what appears to be an arbitrary way.

    for example:
    between 1/03/2010 and 31/03/2010: works pefectly
    between 5/03/2010 and 31/03/2010: shows all results LATER than 31/03/2010
    between 1/04/2010 and 30/04/2010: doesn't apply any filter
    between 5/04/2010 and 30/04/2010: No results. Report is blank


    Same pair of dates always produce the same results. so there must be a pattern, but I can't find it. I am doing something wrong?

    Private Sub Command20_Click()

    Dim strClient As String
    Dim strDateRange As String
    Dim strSQL As String
    'Clear the variable
    strSQL = ""
    'Perform validations
    If IsNull(Me.start) Then
    If Not IsNull(Me.end) Then
    MsgBox "Most date fields must be completed or left blank"
    Exit Sub
    End If
    End If
    If Not IsNull(Me.start) Then
    If IsNull(Me.end) Then
    MsgBox "Most date fields must be completed or left blank"
    Exit Sub
    End If
    End If
    If Not IsNull(Me.start) Then
    If Not IsNull(Me.end) Then
    If DateDiff("d", Me.start, Me.end) < 0 Then
    MsgBox "From date must be prior to or equal to the To date"
    Exit Sub
    End If
    End If
    End If
    If Not IsNull(Me.start) Then
    strDateRange = "[Fecha de Entrada] Between #" & Me.start & "# and #" & Me.end & "#"
    If Len(strSQL) > 0 Then
    strSQL = strSQL & " and " & strDateRange
    Else
    strSQL = strDateRange
    End If
    End If
    If Not IsNull(Me.clte) Then
    strClient = "[ID_Cliente]=" & Me.clte
    If Len(strSQL) > 0 Then
    strSQL = strSQL & " and " & strClient
    Else
    strSQL = strClient
    End If
    End If

    If Len(strSQL) > 0 Then 'Report is filtered
    DoCmd.OpenReport "Relacion Cuentas", acViewPreview, , strSQL
    Else 'No filter
    DoCmd.OpenReport "Relacion Cuentas", acViewPreview
    End If



    End Sub

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,336

  8. #8
    musicalogist is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    10
    oh yeah i see...

    Sorry, but I'm lost again traying to implement a solution. is there a way to specify the format to be used in this code?

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,336
    You mean other than the two methods I just posted? Probably. I just did a db for a client in the UK and used the first method, with this constant:

    Public Const conJetDate As String = "\#mm\/dd\/yyyy\#"

    It is working flawlessly.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  10. #10
    musicalogist is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    10
    thank you, pbaldy.

    I read your links, but I can't figure out how to apply them. I know literally nothing about vba. The database i'm doing is very very simple. The little code I use are either simple commands like requery or code I copied and pasted from somewhere.

    In other words, although Datagopherdan's code works beautifully, I don't really understand what it's doing. So i'm having trouble working in a date format

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,336
    Copy/paste the line I posted above into a standard module (not behind a form or report). Then change this line to:

    strDateRange = "[Fecha de Entrada] Between " & Format(Me.start, conJetDate) & " and " & Format(Me.end, conJetDate)
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  12. #12
    musicalogist is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2010
    Posts
    10
    Pbaldy, Datagopherdan, and Maximus, I sincerely thank you. It is so far working perfectly!

  13. #13
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,336
    Glad we got it sorted out.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Filtering Report with between dates
    By patrickmcdiver in forum Reports
    Replies: 3
    Last Post: 02-22-2010, 10:11 AM
  2. Filtering in a report
    By SIM_2009 in forum Reports
    Replies: 1
    Last Post: 10-06-2009, 03:22 AM
  3. Report Filtering
    By HM1 in forum Reports
    Replies: 2
    Last Post: 07-06-2009, 05:06 AM
  4. Problem filtering a report
    By mrk68 in forum Reports
    Replies: 1
    Last Post: 05-03-2009, 08:31 AM
  5. Filtering my report
    By ldarley in forum Reports
    Replies: 1
    Last Post: 09-05-2008, 08:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums