none
Set a filter in an MS Access sub report RRS feed

  • Question

  • Hi everyone, I am quite inexperienced in Access. 

    The issue I have is my report with multiple sub reports works fine in acviewreport mode. However when I try and run it in pdf mode it produces a blank report..... just a template. When I run it in preview mode, it fails on my filter that I have set within the Open event of the sub report. The code below works fine in viewreport.  

    The report works in pdf mode, when I comment out the below code in the sub report. 

        Me.Filter = "(BankAccountDetails.TransactionDate>" & Tax_Year_Start & ") And (BankAccountDetails.TransactionDate<" & Tax_Year_End & ") And (BankAccountDetails.amount<" & 0 & ") And (BankAccountDetails.type = " & "'Fee'" & ")"
        Me.FilterOn = True

    I don't know if I can do this, but can I set the filter in the main report. Which event would I use to do that? What else can I try?

    THanks in advance. 

    Sunday, November 17, 2019 3:56 AM

All replies

  • Rather than filtering the subform, why not restrict its recordset in the subform's RecordSource query's WHERE clause:

    WHERE BankAccountDetails.TransactionDate > Tax_Year_Start
    AND BankAccountDetails.TransactionDate < Tax_Year_End  
    AND BankAccountDetails.amount < 0  
    AND BankAccountDetails.type = "Fee";

    You don't say what Tax_Year_Start and Tax_Year_End are, but if you want to restrict the rows returned to those in the current accounting year, you can do so with the following function:

    Public Function AcctYearStart(MonthStart As Integer, DayStart As Integer, Optional DateVal As Variant) As Date

        Dim dtmYearStart As Date
        
        If IsMissing(DateVal) Then DateVal = VBA.Date
        
        ' get start of accounting year in year of date value
        dtmYearStart = DateSerial(Year(DateVal), MonthStart, DayStart)
            
        ' if date value is before start of accounting year
        ' accounting year starts year previous to date's year,
        If DateVal < dtmYearStart Then
            dtmYearStart = DateAdd("yyyy", -1, dtmYearStart)
        End If

        AcctYearStart = dtmYearStart
        
    End Function

    If we assume an accounting year starting 1st April for instance, the WHERE clause would thus be:

    WHERE BankAccountDetails.TransactionDate >= AcctYearStart(4,1)
    AND BankAccountDetails.TransactionDate < DateAdd("yyyy",1,AcctYearStart(4,1))  
    AND BankAccountDetails.amount < 0  
    AND BankAccountDetails.type = "Fee";

    Ken Sheridan, Stafford, England

    Sunday, November 17, 2019 10:56 PM
  • Hi Ken,

    Firstly thank you so much for taking the time to try and understand my issue and assist me. 

    To take your response by point:

    1) Rather than filtering the subform, why not restrict its recordset in the subform's RecordSource query's WHERE clause:

    So my filter is dynamic based on the year.  Hence I cannot (or maybe do not know how) restrict it directly on the recordset.  BTW my sub form is based on the table rather than a query. 

    2) You don't say what Tax_Year_Start and Tax_Year_End are, but if you want to restrict the rows returned to those in the current accounting year, you can do so with the following function. 

    Those variables are tax year start and end dates. They are generated by the report based on an input parameter from a form. 


    Regards,

    Gerard


    GEC


    • Edited by GEC00 Tuesday, November 19, 2019 4:38 AM
    Tuesday, November 19, 2019 2:58 AM
  • If you want to restrict the results to a specified accounting year rather than automatically to the current accounting year, you can do this by referencing a control in a form as parameters in the form's RecordSource, which would be a query, not a table.  You only need the start date of the year as  a parameter, the end of the year can be computed by adding a year to the start date with the DateAdd function, and then restring the rows to those with dates before this.  The query's WHERE clause would thus be along these lines:

    WHERE BankAccountDetails.TransactionDate >= Forms![NameOfForm]![Tax_Year_Start]
    AND BankAccountDetails.TransactionDate < DateAdd("yyyy",1,Forms![NameOfForm]![Tax_Year_Start])  
    AND BankAccountDetails.amount < 0  
    AND BankAccountDetails.type = "Fee";

    It is also a good idea to declare the parameter by adding a cause at the start of the query:

    PARAMETERS Forms![NameOfForm]![Tax_Year_Start] DATETIME;
    SELECT etc…..

    For an example of a report output as a PDF file in this way take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file the report (invoice) is based on a query which references the InvoiceNumber control in a form as a parameter.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Tuesday, November 19, 2019 12:22 PM Hyperlink added.
    Tuesday, November 19, 2019 12:22 PM