Filter for subreports

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DDBeards - 22 Apr 2008 10:38 GMT

I have a report for working projects that has two sub reports (done and
pending projects) and a filter menu that allows the user to build a filter
prior to running the report. The filter works great, however I want the
reports filter to be passed to the subreports as well. I have tried several
methods with no success. Any ideas?

DDBeards

Reply to this Message

Duane Hookom - 22 Apr 2008 16:26 GMT

The general solution is to set the criteria in the subreport record sources
to something like:

SELECT ...
FROM ...
WHERE [FieldA] Between Forms!frmCriteria!txtStart and Forms!frmCriteria!txtEnd

Signature

Duane Hookom
Microsoft Access MVP

> I have a report for working projects that has two sub reports (done and
> pending projects) and a filter menu that allows the user to build a filter
[quoted text clipped - 3 lines]
>
> DDBeards

Reply to this Message

DDBeards - 22 Apr 2008 19:08 GMT

Thank you for answering. Isn't there a way to set the subreports filters =
to what the master report's filter is when opened?

DDbeards

> The general solution is to set the criteria in the subreport record sources
> to something like:
[quoted text clipped - 10 lines]
> >
> > DDBeards

Reply to this Message

Duane Hookom - 22 Apr 2008 20:20 GMT

You can use the method that I suggested for both the main report and the
subreport. I would not attempt to mess around with filter or record source
properties of the subreport once it has started to open/display.

Signature

Duane Hookom
Microsoft Access MVP

> Thank you for answering. Isn't there a way to set the subreports filters =
> to what the master report's filter is when opened?
[quoted text clipped - 15 lines]
> > >
> > > DDBeards

Reply to this Message

DDBeards - 22 Apr 2008 20:53 GMT

My master report is opened with the following statement: "DoCmd.OpenReport
stDocName, acViewPreview, , sFilter, acWindowNormal" where the sFilter is
built through a menu system therefore the filter could be any number of where
posibilities! I really think the only way is going to be some way of seting
the report.filter = to the master's report.filter? Maybe I am not getting
your suggestion, plese don't give up on me.

Chris

> You can use the method that I suggested for both the main report and the
> subreport. I would not attempt to mess around with filter or record source
[quoted text clipped - 19 lines]
> > > >
> > > > DDBeards

Reply to this Message

Duane Hookom - 22 Apr 2008 21:46 GMT

If the sFilter is dynamic and sufficiently complex, I would base the
subreports on saved queries. Then build the query SQL on the fly using the
same sFilter and set the SQL property of the saved query. For instance if
subreportA has a record source of "qselSubRptA" and similar for subreportB:

'need a reference to the DAO object library
Dim db as DAO.Database
Dim qd as DAO.QueryDef
Dim strSQL as String
Set db = CurrentDb
Set qd = db.QueryDefs("qselSubRptA")
strSQL = "SELECT .... FROM .... WHERE " & sFilter
qd.SQL = strSQL
Set qd = db.QueryDefs("qselSubRptB")
strSQL = "SELECT .... FROM .... WHERE " & sFilter
qd.SQL = strSQL
Set qd = Nothing
Set db = Nothing
' Then open your report with the new SQL'd subreports.

I keep a small function in most of my apps that changes the SQL property of
any saved query.

Signature

Duane Hookom
Microsoft Access MVP

> My master report is opened with the following statement: "DoCmd.OpenReport
> stDocName, acViewPreview, , sFilter, acWindowNormal" where the sFilter is
[quoted text clipped - 28 lines]
> > > > >
> > > > > DDBeards

Reply to this Message

DDBeards - 23 Apr 2008 13:13 GMT

OK Duane I following you, but now have a basic question about writing a long
SQL statement. The following is my statement combined with your idea:

strSQL = "SELECT Tbl_PTS_M.TSR_NO AS TSR, Tbl_PTS_M.PTS_Title AS Title,
Tbl_PTS_M.PTS_Comp_Date AS [Comp], LTbl_Emp_M.Emp_Name AS Dev,
Tbl_PTS_M.TSR_TA_UserID AS TA, LTbl_Serv_M.Serv_Name AS Service,
Tbl_PTS_M.PTS_Req_Dept AS CustDept, &_"
LTbl_Emp_M_1.Emp_Name AS CustName" FROM ((Tbl_PTS_M LEFT
JOIN LTbl_Emp_M ON Tbl_PTS_M.PTS_Dev_ID = LTbl_Emp_M.Emp_Id) LEFT JOIN
LTbl_Emp_M AS LTbl_Emp_M_1 ON Tbl_PTS_M.PTS_Req_ID = LTbl_Emp_M_1.Emp_Id)
LEFT JOIN LTbl_Serv_M ON &_
Tbl_PTS_M.PTS_Service = LTbl_Serv_M.Serv_ID &_
WHERE (((Tbl_PTS_M.PTS_Comp_CK) = True) And
((Tbl_PTS_M.PTS_Active_CK) = True) And ((Year([PTS_Comp_Date])) =
Year(Now())) And ((Tbl_PTS_M.PTS_MM_CK) = False)) And " & sFilter"

I can not figure out how to split it up among lines. The compiler keeps
putting " at the end of each line. I know this is basic question, but I can
not get it.

Thanks in advance.

DDBeards

> If the sFilter is dynamic and sufficiently complex, I would base the
> subreports on saved queries. Then build the query SQL on the fly using the
[quoted text clipped - 51 lines]
> > > > > >
> > > > > > DDBeards

Reply to this Message

Duane Hookom - 23 Apr 2008 14:48 GMT

I usually break long SQL statements into sections of SELECT, FROM, WHERE,
GROUP BY, and ORDER BY sections. I also use the line continuations to avoid
issues with word wrap in news groups and make the code more readable. The
line continuation syntax uses a double-quote space ampersand space and
underscore. Make sure you have spaces either before your final " or after
your first. I think I got all of this correct:

strSQL = "SELECT Tbl_PTS_M.TSR_NO AS TSR, " & _
"Tbl_PTS_M.PTS_Title AS Title, " & _
"Tbl_PTS_M.PTS_Comp_Date AS [Comp], " & _
"LTbl_Emp_M.Emp_Name AS Dev, " & _
"Tbl_PTS_M.TSR_TA_UserID AS TA, " & _
"LTbl_Serv_M.Serv_Name AS Service, " & _
"Tbl_PTS_M.PTS_Req_Dept AS CustDept, " & _
"LTbl_Emp_M_1.Emp_Name AS CustName "

strSQL = strSQL & "FROM ((Tbl_PTS_M LEFT JOIN LTbl_Emp_M " & _
"ON Tbl_PTS_M.PTS_Dev_ID = LTbl_Emp_M.Emp_Id) " & _
"LEFT JOIN LTbl_Emp_M AS LTbl_Emp_M_1 " & _
"ON Tbl_PTS_M.PTS_Req_ID = LTbl_Emp_M_1.Emp_Id) " & _
"LEFT JOIN LTbl_Serv_M ON " & _
"Tbl_PTS_M.PTS_Service = LTbl_Serv_M.Serv_ID "

strSQL = strSQL & "WHERE (Tbl_PTS_M.PTS_Comp_CK = True " & _
"And Tbl_PTS_M.PTS_Active_CK = True " & _
"And Year([PTS_Comp_Date]) = Year(Now()) " & _
"And Tbl_PTS_M.PTS_MM_CK = False And " & sFilter

Signature

Duane Hookom
Microsoft Access MVP

> OK Duane I following you, but now have a basic question about writing a long
> SQL statement. The following is my statement combined with your idea:
[quoted text clipped - 75 lines]
> > > > > > >
> > > > > > > DDBeards

Reply to this Message

John Spencer - 23 Apr 2008 14:49 GMT

Add " ampersand space underscore at the end of each line

strSQL = "SELECT Tbl_PTS_M.TSR_NO AS TSR" & _
", Tbl_PTS_M.PTS_Title AS Title" & _
",Tbl_PTS_M.PTS_Comp_Date AS [Comp]" & _
", LTbl_Emp_M.Emp_Name AS Dev" & _
", Tbl_PTS_M.TSR_TA_UserID AS TA" & _
", LTbl_Serv_M.Serv_Name AS Service" & _
", Tbl_PTS_M.PTS_Req_Dept AS CustDept" & _
", LTbl_Emp_M_1.Emp_Name AS CustName" & _
" FROM ((Tbl_PTS_M LEFT JOIN LTbl_Emp_M " & _
" ON Tbl_PTS_M.PTS_Dev_ID = LTbl_Emp_M.Emp_Id) " & _
" LEFT JOIN LTbl_Emp_M AS LTbl_Emp_M_1 " & _
" ON Tbl_PTS_M.PTS_Req_ID = LTbl_Emp_M_1.Emp_Id) " & _
" LEFT JOIN LTbl_Serv_M " & _
" ON Tbl_PTS_M.PTS_Service = LTbl_Serv_M.Serv_ID" & _
" WHERE (((Tbl_PTS_M.PTS_Comp_CK) = True) And " & _
" ((Tbl_PTS_M.PTS_Active_CK) = True) And " & _
" ((Year([PTS_Comp_Date])) = Year(Now()))" & _
" And ((Tbl_PTS_M.PTS_MM_CK) = False)) And " & sFilter

I also often break this up into sections for easier editing.

strSQL = "SELECT Tbl_PTS_M.TSR_NO AS TSR" & _
", Tbl_PTS_M.PTS_Title AS Title" & _
",Tbl_PTS_M.PTS_Comp_Date AS [Comp]" & _
", LTbl_Emp_M.Emp_Name AS Dev" & _
", Tbl_PTS_M.TSR_TA_UserID AS TA" & _
", LTbl_Serv_M.Serv_Name AS Service" & _
", Tbl_PTS_M.PTS_Req_Dept AS CustDept" & _
", LTbl_Emp_M_1.Emp_Name AS CustName"

StrSQL = StrSQL & _
" FROM ((Tbl_PTS_M LEFT JOIN LTbl_Emp_M " & _
" ON Tbl_PTS_M.PTS_Dev_ID = LTbl_Emp_M.Emp_Id) " & _
" LEFT JOIN LTbl_Emp_M AS LTbl_Emp_M_1 " & _
" ON Tbl_PTS_M.PTS_Req_ID = LTbl_Emp_M_1.Emp_Id) " & _
" LEFT JOIN LTbl_Serv_M " & _
" ON Tbl_PTS_M.PTS_Service = LTbl_Serv_M.Serv_ID"

StrSQL = strSQL & _
" WHERE (((Tbl_PTS_M.PTS_Comp_CK) = True) And " & _
" ((Tbl_PTS_M.PTS_Active_CK) = True) And " & _
" ((Year([PTS_Comp_Date])) = Year(Now()))" & _
" And ((Tbl_PTS_M.PTS_MM_CK) = False)) And " & sFilter

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

Reply to this Message

Duane Hookom - 23 Apr 2008 15:11 GMT

John,
Reviewing our replies suggests we spent too much time together last week at
the MVP Summit. About 1,000 miles apart and your only issue is where you
place your line continuations ;-)

Signature

Duane Hookom
Microsoft Access MVP

> Add " ampersand space underscore at the end of each line
>
[quoted text clipped - 126 lines]
> >>>>>>>
> >>>>>>> DDBeards

Reply to this Message

John Spencer - 23 Apr 2008 18:02 GMT

Didn't mean to reply for you. Just saw this one open and thought you were
otherwise occupied - so answered it.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> John,
> Reviewing our replies suggests we spent too much time together last week at
> the MVP Summit. About 1,000 miles apart and your only issue is where you
> place your line continuations ;-)

Reply to this Message

Duane Hookom - 23 Apr 2008 21:36 GMT

I appreciate your replies and it's good to compare answers once in a while.
Jump in any time.

Signature

Duane Hookom
Microsoft Access MVP

> Didn't mean to reply for you. Just saw this one open and thought you were
> otherwise occupied - so answered it.
[quoted text clipped - 8 lines]
> > the MVP Summit. About 1,000 miles apart and your only issue is where you
> > place your line continuations ;-)

Reply to this Message