Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37

    Multi criteria filter a Report by listboxes and comboboxes

    Hello all,



    Hope you can help me with the following:

    I would like to implement a multi criteria filter for filtering a report based on various comboboxes and listboxes.
    The filter I have so far is working perfect and is based on the sales report filter from the NorthWind database, however I would like to add some more criteria trough comboboxes.

    So imagine that I would like to add another combobox in the Northwind Sales Report Dialog, how should I implement this into the vba code?
    Can somebody give me a few/small examples so I can figure it out.

    I tried to add another Grouping field on the report and add reference code to this particular field, however it is not working.
    I already added an additional combo-box which will be occupied by another "Filter row source" in the Sales reports table.

    What I have so far is a dialog with the following selection possibilities: Quantity non conforming parts (NCR disposition) with status "Accept", "Reject", "Rework" (1st Filter items) from a particular supplier (2nd Filter items).
    See below figure.

    Click image for larger version. 

Name:	Filter dialog.JPG 
Views:	38 
Size:	40.9 KB 
ID:	26183


    some info regaring my access version:
    MS access 2015 X64

    Hope someone can put me on the right track for this!

    Thanks

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You must add vb the build the filter by looking at all the criteria,
    then open the report....
    Code:
    sub btnOpenRpt()
     Dim sWhere as string.
    
    If not isNull(txtBox1) then sWhere =sWhere & "[state]='" & txtBox1 & "' and "
    If not isNull(txtBox2) then sWhere =sWhere & "[name]='" & txtBox2 & "' and "
    'remove last AND
    sWhere = left(sWhere,len(sWhere)-5)
    
    docmd.openReport "rMyReport",,sWhere
    end sub

  3. #3
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Thank you Ranman,
    I understand your way of filtering, however, I did it the way how it is filtered in Northwind example. It is done by SQL and this is already running quite good. But now I would like to add an additional combobox filter.

    Could you please have a look intothe Northwind example and tell me how to proceed?
    Thank you in advance

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    i dont see how that can work. any number of controls could have / or Not have data.
    The query cant see this.

  5. #5
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Goodmorning Ranman,
    For your information I attached the code from MS Northwind.

    Northwind has an additional table, called: Sales Reports and contains the following information:

    Click image for larger version. 

Name:	Sales Reports Table.png 
Views:	33 
Size:	21.0 KB 
ID:	26201

    Under the "Review report" button of the filter dialog the following code can be found:


    Sub PrintReports(ReportView As AcView)
    ' This procedure used in Preview_Click and Print_Click Sub procedures.
    ' Preview or print report selected in the ReportToPrint option group.
    ' Then close the Print Sales Reports Dialog form.
    Dim strReportName As String
    Dim strReportFilter As String
    Dim lOrderCount As Long


    ' Determine report filtering
    If Nz(Me.lstReportFilter) <> "" Then
    strReportFilter = "([SalesGroupingField] = """ & Me.lstReportFilter & """)"
    End If

    ' Determine reporting time frame
    Select Case Me.lstSalesPeriod
    Case ByYear
    strReportName = "Yearly Sales Report"
    lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear)
    Case ByQuarter
    strReportName = "Quarterly Sales Report"
    lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear & " AND [Quarter]=" & Me.cbQuarter)
    Case ByMonth
    strReportName = "Monthly Sales Report"
    lOrderCount = DCountWrapper("*", "Sales Analysis", "[Year]=" & Me.cbYear & " AND [Month]=" & Me.cbMonth)
    End Select

    If lOrderCount > 0 Then
    TempVars.Add "Group By", Me.lstSalesReports.Value
    TempVars.Add "Display", DLookupStringWrapper("[Display]", "Sales Reports", "[Group By]='" & Nz(Me.lstSalesReports) & "'")
    TempVars.Add "Year", Me.cbYear.Value
    TempVars.Add "Quarter", Me.cbQuarter.Value
    TempVars.Add "Month", Me.cbMonth.Value

    eh.TryToCloseObject
    DoCmd.OpenReport strReportName, ReportView, , strReportFilter, acWindowNormal
    Else
    MsgBoxOKOnly NoSalesInPeriod
    End If
    End Sub

    Under the report "On open" action, the following code is present:

    Private Sub Report_Open(Cancel As Integer)
    On Error GoTo ErrorHandler

    Dim strSQL As String

    If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or IsNull(TempVars![Year]) Then
    DoCmd.OpenForm "Sales Reports Dialog"
    Cancel = True
    Exit Sub
    End If


    strSQL = "TRANSFORM CCur(Nz(Sum([Sales]),0)) AS X"
    strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as SalesGroupingField FROM [Sales Analysis] "
    strSQL = strSQL & " Where [Year]=" & TempVars![Year]
    strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" & TempVars![Display] & "]"
    strSQL = strSQL & " Pivot [Sales Analysis].[Quarter] In (1,2,3,4)"

    Me.RecordSource = strSQL
    Me.SalesGroupingField_Label.Caption = TempVars![Display]

    Done:
    Exit Sub
    ErrorHandler:
    ' Resume statement will be hit when debugging
    If eh.LogError("Yearly Sales Report_Open", "strSQL = " & strSQL) Then
    Resume
    Else
    Cancel = True
    End If
    End Sub

    The question now is, When I would like to add another combo- or listbox, where should I add the vba code and how should I do this.
    I understand the above code generally, however, until now I did not succeed.

    Ruud

  6. #6
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Is there really nobody who can help me?

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    This isn't the way I would have done it. I would create a global function called CreateReportFilter and call it when the report is opened. The routine would create the filter string for the report by concatenating all the filters on the form.

  8. #8
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Hello Aytee111,
    That sounds indeed as a fundamental solution, however I am a novice programmer, so unfortunately I need some help with this.
    Could you please show me an example?
    Ruud

  9. #9
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    In the OnOpen event for your reports, add this:

    Code:
    Private Sub Report_Open(Cancel As Integer)
        Me.Filter = CreateReportFilter()
        Me.FilterOn = True
        Me.Requery
        
    End Sub
    In an outside module (in VBA, click on Insert Module:

    Code:
    Public Function CreateReportFilter()
        Dim FilterStr As String
        
        If Not IsNull(Forms!Myformname!filter1) Then
            FilterStr = "field1='" & Forms!Myformname!filter1 & "'"
            If Not IsNull(Forms!Myformname!filter2) Then
                FilterStr = FilterStr & " AND filter2='" & Forms!Myformname!filter2 & "'"
            End If
        Else
            If Not IsNull(Forms!Myformname!filter2) Then
                FilterStr = "filter2='" & Forms!Myformname!filter2 & "'"
            End If
        End If
            
        Debug.Print FilterStr
        CreateReportFilter = FilterStr
    End Function

  10. #10
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sorry, error in code, here is what it should be:

    Code:
    Public Function CreateReportFilter()
        Dim FilterStr As String
        
        If Not IsNull(Forms!Myformname!filter1) Then
            FilterStr = "field1='" & Forms!Myformname!filter1 & "'"
            If Not IsNull(Forms!Myformname!filter2) Then
                FilterStr = FilterStr & " AND field2='" & Forms!Myformname!filter2 & "'"
            End If
        Else
            If Not IsNull(Forms!Myformname!filter2) Then
                FilterStr = "field2='" & Forms!Myformname!filter2 & "'"
            End If
        End If
            
        Debug.Print FilterStr
        CreateReportFilter = FilterStr
    End Function

  11. #11
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Hi Aytee111,
    Thank you for the code, I will try this example tomorrow.
    But I think it is not exactly what I need, while the filter as shown from Northwind will also calculate qty per year/month or quarter.
    Besides of this, from the actual Northwind fiilter, I only need just 1 general unbound form, all the variables will be selected by the selection dialog.
    Anyway, thank you for your help so far, I will try this and keeps this in mind for future reports.
    Ruud

  12. #12
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Perhaps you can supply a sample database, this really shouldn't be too hard to solve but without your object names etc it's going to be hard to give you code.

    Personally, when I have multiple potential controls that are going to determine the outcome of the report I cycle through the controls to pick them up, that way if you add criteria or filters along the way it's much easier to handle them without having to re-write your code.

  13. #13
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Hello rpeare,
    I will make a sample database tomorrow.
    Thank you very much for your reply.
    Ruud

  14. #14
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37

    DB Example Report Filter

    DB_Report_Filter.zip

    Guys,

    You can find attached the example of my report filter.
    I already added the additional combobox filter "Filter Vendors". however this is not working yet.

    So what I would like is:
    select the needed filters and click on preview to view the report. If nothing is selected than everything should be shown.
    Until now, all the filters are working, except "Filter Vendors".

    You could do me a real favor to help me with this.

    thanks in advance,
    Ruud

  15. #15
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    This seems unnecessarily complex for what you're doing.

    That aside use this code in the ON OPEN of the report YEARLY NCR REPORT instead of what you're currently using

    Code:
        On Error GoTo ErrorHandler    
        Dim strSQL As String
        
        If IsNull(TempVars![Display]) Or IsNull(TempVars![Group By]) Or IsNull(TempVars![Year]) Then
            DoCmd.OpenForm "frmNCRReportsDialog"
            Cancel = True
            Exit Sub
        End If
    
    
        strSQL = "TRANSFORM CCur(Nz(Sum([NCR]),0)) AS X"
        strSQL = strSQL & " SELECT [" & TempVars![Display] & "] as NCRGroupingField FROM [qryNCRAnalysis] "
        strSQL = strSQL & " Where [Year]=" & TempVars![Year]
        If Not IsNull(Forms!frmncrreportsdialog!lstVendorFilter) Then
            strSQL = strSQL & " AND [Vendor]='" & Forms!frmncrreportsdialog!lstVendorFilter & "'"
        End If
        strSQL = strSQL & " GROUP BY [" & TempVars![Group By] & "], [" & TempVars![Display] & "]"
        strSQL = strSQL & " Pivot [qryNCRAnalysis].[Quarter] In (1,2,3,4)"
        
        Me.RecordSource = strSQL
        Me.NCRGroupingField_Label.Caption = TempVars![Display]
        
    Done:
        Exit Sub
    ErrorHandler:
        MsgBox Err.Description
        Resume Done
    The problem is that your 'final' version of the query does not contain the vendor name so you have to make it part of the criteria driving the generated crosstab.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-31-2016, 12:16 PM
  2. Report filter form using multiple comboboxes.
    By Stephenson in forum Programming
    Replies: 9
    Last Post: 10-24-2015, 10:00 AM
  3. Replies: 6
    Last Post: 05-15-2013, 03:36 PM
  4. Replies: 1
    Last Post: 02-12-2013, 10:41 AM
  5. Replies: 5
    Last Post: 02-05-2013, 01:18 PM

Tags for this Thread

Posting Permissions

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