Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Filtering (Access XP)

    I have created a main report with 4 subreports. The subreports are in the
    report footer because the subreports must be grouped but the main report is
    not grouped. There are relationships between the tables that are used in the
    report.
    I have used the ApplyFilter macro action to filter the main and subreports.
    The filter works fine for the main report but only pulls up one record for
    the subreports. My test data has three records that fit the criteria. It's
    as if the filter found the first matching record and then stops. The filter
    is based on a parameter query. When I run the parameter query by itself, the
    correct number of records are returned. I'm trying to stay away from coding,
    but will use it if forced to by Access.
    --
    Thanks for your help in advance...

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

    Re: Report Filtering (Access XP)

    Are there relations between the main report and the subreports ?
    If so, the subreports will only give you the records related to the last record in the main report as the subreport are in his the footer .
    Francois

  3. #3
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Filtering (Access XP)

    The only relationships set up are between the tables, which sets up the queries to be related and the report and subreports are based on the queries. Other than that, I have not physically set up Master/Child Links within the report properties.
    Thanks

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

    Re: Report Filtering (Access XP)

    If the subreports are not linked to the main report, the subreport should give the same records as the queries they are based on.
    I suspect there is something wrong with your macro setting the filter.
    Francois

  5. #5
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Filtering (Access XP)

    The macro setting the filter is: Macro Action -- ApplyFilter, Filter Name -- qryReportDateRange. qryReportDateRange is a parameter query on a date field which prompts the user to input a start date and an end date --- Between [Enter Quarter Start Date] And [Enter Quarter End Date]. There is no where condition on the ApplyFilter macro action. The macro is applied to the main report OnOpen Event. The filter that is applied is as follows:

    (((((tblCSAQtrActivity.[Submit Date]) Between [Enter Quarter Start Date] And [Enter Quarter End Date])))) AND (((((tblCSAQtrActivity.[Submit Date]) Between [Enter Quarter Start Date] And [Enter Quarter End Date]))))

    The FilterOn property is = to Yes for the main and sub reports.

    When I run the query that is run by the macro, the records are correct for the date range input to the parameter query. When I remove the ApplyFilter macro and just place a parameter on the report recordsource query (same as what the macro fires), I just get continual parameter prompts.

    I'm stumped. I've tried to pare down the database to include, but it's too large even when zipped.

    Thanks in advance

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

    Re: Report Filtering (Access XP)

    Is there a filter saved with the (sub)report ? In design view, select the report and open the property window. In the Data tab, on the line Filter, is there some condition ? Remove it.
    You shouldn't do any filtering in the report as the selection is done in the query with your parameters.
    You can look at Hans's <post#=401925>post 401925</post#> to reduce the size of the db for attaching. If you can't go under the 100k, you can also send it to my e-mail address (see my profile), i'll have a look at it.
    Francois

  7. #7
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Filtering (Access XP)

    I removed the filter - access placed that in automatically. I also turned the FilterOn properties to No for all reports to no avail. I sent you a copy of the database. Thanks for taking the time to help.

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

    Re: Report Filtering (Access XP)

    The four subreports are linked to the main report by the ID field? Is the the purpose ?
    Or do you want the five reports all filterd on the same Between Start and End Date ?
    Francois

  9. #9
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Filtering (Access XP)

    I want the reports to filter on the same start and end dates. the tables are linked on ID so I could establish a relationship between them. Are you suggesting placing a submit date field into each of the tables and then establish the relationship between this field?

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

    Re: Report Filtering (Access XP)

    In attachment the modified db.
    I put two texboxes on your form and modify the queries to look at the two textboxes on these forms. I remove the linking between the main report and subreports as they have no sense and I remove the macro in the on open event as the filtering is now in each query.
    Can you check if this is what you want ?
    To attach the db I've done a compact & repair.
    Francois

  11. #11
    New Lounger
    Join Date
    Jan 2004
    Location
    Lawrence, Massachusetts, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Filtering (Access XP)

    That is a beautiful thing! That is exactly what I need for the report. Thank you so much. And I have learned a couple of things too! Thank you, Francois, for taking the time.

Posting Permissions

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