filter on subreport

Question

I have a report (rpt_los_docreport) with a subreport whose name on the report is subrpt_los_doc_individual that has its origin as subrpt_los_doc_individualAfter7_31_2010.  I run the following code:

        DoCmd.OpenReport stDocName, acViewDesign, , "subrpt_los_doc_individual!los_beg_time = #" & TimeValue(ctrlList.Column(3, intCurrRow)) & "# and subrpt_los_doc_individual!los_service_date = #" & DateValue(ctrlList.Column(3, intCurrRow)) & "#"

        DoCmd.Close acReport, stDocName, acSaveYes

        DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, stOutputFile

        SetAttr stOutputFile, vbReadOnly

which takes a date value from a list box on main form and a time value from same list box and attempts to use that as filters.

the report runs as if no filters except member number is present.  I have run the underlying query of the subform with actual date and time that would be passed to it and it pulls one record as it should.  What is wrong with the above code.  Thank you.

Answer 1

Are you attempting to filter the parent report on the basis of criteria applied to the subreport, i.e. return only those rows in the parent report where there is at least one row in the instance of the subreport for that row which matches the criteria?  If so, what you have to do is include a subquery in the OpenReport method's WhereCondition argument where the subquery returns the primary key values of the parent report which match the criteria applied to the subquery.  For example, with a report based on an Items table with a subreport based on a query which joins the ItemSuppliers and Suppliers table so as to list the suppliers per item, to filter the report by supplier (entered as a parameter at runtime) it would be called like this:

DoCmd.OpenReport "rptItemSuppliers", View:=acViewPreview, WhereCondition:="ItemID IN(SELECT ItemID FROM ItemSuppliers INNER JOIN Suppliers ON ItemSuppliers.SupplierID = Suppliers.SupplierID WHERE Supplier = [Enter supplier:])"

In your case, however, you are trying to change the report definition so that it is restricted to the criteria.  To do this you would not use the WhereCondition argument of the OpenReport method, but open the report in design view, change its RecordSource property so that it includes the IN operator and a subquery along the lines of the above, concatenating the date literals into the subquery.  Then close and save the report before outputting it to the PDF file.

A further point is that your query includes a lot of outer joins.  Life is too short for me to be able to analyse your SQL statement in detail, but are you sure that in the WHERE clause you are not including any restrictions on the right side of a LEFT OUTER JOIN, or the left side of a RIGHT OUTER JOIN?  That won't work as it in effect makes the join an INNER JOIN, so any unmatched rows will not be returned.

Answer 2

i did a closer exam of the query and I had put two dates and two times in the los_service.los_service_date and los_service.los_beg_time.  I took those out and the query now runs and provides the data needed.  microscopes and/or magnifying glasses are good at times.  Thanks for all of your great wisdom.

Answer 3

I am pulling my hair out.  following is the code:

        strsql = "SELECT tblStaff.StaffName, tblMemberTxPlanGoals.Goal, los_service.service, los_service.los_service_date, " & _

                 "los_service.los_staff, los_service_entry.los_service_entry_note, los_service_entry.los_service_id, " & _

                 "los_service.member_number, los_service_entry.FSA, los_service.los_beg_time, los_service.nbr_of_minutes, " & _

                 "levels_of_service.ind_service, DatePart('m',[los_service_date]) AS los_month, tbl_cm_location.locdesc, " & _

                 "member_personal_goal.Goal AS personalGoal " & _

                 "FROM tblMemberTxPlanGoals RIGHT JOIN ((((tblStaff INNER JOIN (los_service INNER JOIN los_service_entry ON " & _

                 "los_service.ID = los_service_entry.los_service_id) ON tblStaff.StaffNumber = los_service.los_staff) INNER JOIN " & _

                 "levels_of_service ON los_service.service = levels_of_service.service) INNER JOIN tbl_cm_location ON " & _

                 "los_service.locnbr = tbl_cm_location.locnbr) LEFT JOIN (member_personal_goal_xref LEFT JOIN member_personal_goal " & _

                 "ON member_personal_goal_xref.goal_id = member_personal_goal.goal_id) ON los_service.ID = member_personal_goal_xref.los_id) " & _

                 "ON (tblMemberTxPlanGoals.MemberNumber = los_service.member_number) AND (tblMemberTxPlanGoals.Service = los_service.service) " & _

                 "WHERE (((los_service.service) Not In ('PR-AG','PRCG','PS-IND','PS-GROUP')) AND ((los_service.los_service_date)=#2/10/2011#) " & _

                 "AND ((los_service.los_staff) Not In (6,132)) AND ((los_service.member_number)=[Reports]![rpt_los_docreport]![MemberNumber]) " & _

                 "AND ((los_service.los_beg_time)=#12/30/1899 14:56:0#) AND ((levels_of_service.ind_service)=Yes) " & _

                 "AND ((DatePart('m',[los_service_date]))=[Reports]![rpt_los_docreport]![rpt_month]) " & _

                 "AND ((DatePart('yyyy',[los_service_date]))=[Reports]![rpt_los_docreport]![rpt_year])) " & _

                 "AND los_service.los_service_date = #" & DateValue(ctrlList.Column(3, intCurrRow)) & _

                 "# AND los_service.los_beg_time = #" & TimeValue(ctrlList.Column(3, intcurrow)) & "#"

        DoCmd.OpenReport "subrpt_los_doc_individualAfter7_31_2010", acViewDesign

        Reports!subrpt_los_doc_individualAfter7_31_2010.RecordSource = strsql

        DoCmd.Close acReport, "subrpt_los_doc_individualAfter7_31_2010", acSaveYes

the report comes up with nothing in the report where the subreport should be.  Thanks for wisdom but I believe i need more.

Answer 4

It looks like you are trying to filter based on the subreport, not the underlying Recordsource. Also, I'm not sure you can even do that. If you can't filter the main report, you may have to use Criteria to filter the recordsource of the subreport rather than a WHERE clause.