Totals in a subreport

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Sales Order report showing total sales by product group within
customer
I have included a subreport in the report footer section in which I show the
overall total for each product group as well as a total for the report.
When I filter the report to only show a specific product group the subreport
shows all product groups in the record set rather than the selected product
group.

strStartDate = "OrderDate >= Forms![Sales Order Report
Dialogue]!StartDate And OrderDate <= Forms![Sales Order Report
Dialogue]!EndDate"
strWhereProd = "SOProductGroup = Forms![Sales Order Report
Dialogue]!SelectProdGroup"
SOStatus = " And xstep3 = ""O"" "

DoCmd.OpenReport "Sales Orders Report", PrintMode, , strWhereProd & " And "
& strStartDate & SOStatus

How to I get this strWhereProd & " And " & strStartDate & SOStatus to act
on the subreport.
 
Since you are opening this report from a form that already contains the
filter info, could you open the subreport's query and have it read the
criteria from the form? Something like this in the Criteria row under the
group field:
Forms![Sales Order Report Dialogue]!SelectProdGroup

If that's not practical and the report is always opened through the form,
you could reassign the SQL property of the subreport's query:
CurrentDb().QueryDefs("Query2").SQL = "SELECT ...

If you just need a total in the subreport, you can bring the subtotal from
the subreport back onto the main report, and use a running sum to aggregate
it over the report. That won't list the correct items though.
 
Back
Top