Limiting Report to certain Dates

[Varda]

I looked at the link you sent, but am overwhelmed a bit.



How would I achieve what I'm trying to do?

1. I have a form where I enter txtStartDate and txtEndDate.
2. On the form is a button with code on it.
3. When clicking the button I want a report called rptAllItems_Summary_SelectDates to open in Preview mode.
4. I want the Subreport within it to show the records that fall within the range of dates selected on the Form. NOTE: The subreport's name is rptAllItems_Summary_Done; the control's name within the main report is called ctrl_CompletedItems.

The code I copied from another website, and it worked fine when the information was not inside a subreport. But now that it's in a subreport, I don't know how to compare the fields from the FORM, to the two fields on the SUBREPORT.

I referred to the field within the subreport in the following manner:

strDateFieldStart = "Reports![rptAllItems_Summary_SelectDates]![ctrl_CompletedItems].Report![ItemStartDate]"


The result of this seem to completely ignore the code, and it shows me all records. My only suspicion is that I'm not referencing the fields inside the subreport correctly, and it's ignoring the entire code as a result.

[June7]

Did you read my previous post?

[Varda]

The main report has no record source. No data. It's just a "carrier" for three subreports.

I am just trying to limit the data in ONE of those subreports to reflect only records within a date range selected on the form.

Your sentence "You say you want to apply date range filter to subreport, yet the code appears to apply filter to main report" - that's exactly what I'm not understanding. I thought the code is applying the filter to the SUBREPORT.

Your next sentence "And why would the date variables be set by reference to subreport which isn't even open?" - I think that is the explanation as to why my code isn't working. I guess I need to first "open" the subreport and only then reference the fields in it for the purpose of comparison.


I'm going to attempt placing dynamic parameters in the query that is the subreport's record source.

Is that the only possible solution?

[June7]

The code should trigger run-time error because it references an object that is not open.

Did you note my comment about dynamic parameterized query?

Setting filter of report or subreport after it opens might not be possible. If it is, requires setting Filter property and then a Requery. I know this can be done with forms but don't remember about reports. Maybe in Report view but not PrintPreview.

Still, why would you reference fields of subreport as filter criteria for itself - makes no sense.

[Varda]

"Still, why would you reference fields of subreport as filter criteria for itself - makes no sense."

I'm not sure what you mean by "...criteria for itself."

I have two fields on the form: txtStartDate and txtEndDate.

I have a subreport that has two fields for each record called ItemStartDate and ItemEndDate.

I'm trying to preview a report, in a way that shows the subreport "filtered" to reflect start and end dates that are limited in the range dictated by the user data entry on the form.

To explain this in more detail:
This database tracks any activities done this past week. Each activity has a start date and end date. The user will select a start date and end date on a form, and the subreport should reflect only the activities that were active during that period of time. If the activities were closed before the start date, or if they started after the end date - then they shouldn't appear on the subreport at all.
It has to be a subreport because it is only one part of the main report, and because I'm doing something else on this subreport that looks all screwy if I put it on the main report (vertical line on the detail section).

[Varda]

June7, I responded to your posts throughout this thread.


I finally reached the solution for the problem by using the parameters in the query which is the record source for the subreport to compare with the controls on the form.

Of course it means I won't be able to use this subreport independetly for anything else, but that's okay. As long as it works right now and performs the function I wanted.


Thank you for everyone's help!

Varda

[June7]

Your code references report, not form, to pull filter parameters.

strDateFieldStart = "Reports![rptAllItems_Summary_SelectDates]![rptAllItems_Summary_Done].Report![ItemStartDate]"
strDateFieldEnd = "Reports![rptAllItems_Summary_SelectDates]![rptAllItems_Summary_Done].Report![ItemEndDate]"

Then it uses those parameters to build the filter string used in WHERE argument of opening main report.

DoCmd.OpenReport strReport, lngView, , strWhere


Dynamic parameterized query was suggested in post 15.

[Varda]

June7, yes, I saw it, and that is why I went that route. :-) Thank you for suggesting it!