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
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
Duane Hookom said:
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.
--
Duane Hookom
Microsoft Access MVP
:
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
properties of the subreport once it has started to open/display.
--
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?
DDbeards
:
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
--
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
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
