Need help adding additional criteria

[SMT]

Good afternoon,

The query I have listed below worked perfectly. Then I need to add some
additional criteria and I just cant seem to get that in correctly. I had to
add an actual_complete_date to the P table. And now not only do I need the
latest record from F, I also need to show records for 1 reporting period
after actual completion date. The reporting dates are listed in
tblDateSubmitted.DateReport.

Original Query
SELECT PM.proj_mgr_branch AS [Proj Mgr Code], P.project_title AS Project,
PM.proj_mgr_name AS [Proj Mgr Name], P.proj_risk_level AS [Project Risk
Level], C.cust_adv_name AS [Customer Advocate], E.ee_description AS
[Enterprise Executive (EE)], F.planned_funding AS [Funding to spend in
FY07($K) (From Carryover & New Orders)], F.bcwp AS [BCWP ($K)], F.bcws AS
[BCWS ($K)], F.acwp AS [ACWP ($K)], IIf([bcwp]<>0 And Not
IsNull([bcwp]),CDbl([bcwp])/CDbl([bcwp])) AS SPI, IIf([bcwp]<>0 And Not
IsNull([bcwp]),CDbl([bcwp])/CDbl([acwp])) AS CPI, F.method_of_calculation AS
[Method of Calculation], F.comments AS Comments, F.spi_narrative AS [SPI
Narrative], F.cpi_narrative AS [CPI Narrative], F.carryover_funds AS
[Estimated Carry-over ($k)]
FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER JOIN
(customer_advocate AS C INNER JOIN (project_execution_metrics AS P INNER JOIN
funding_history AS F ON P.proj_exec_id = F.proj_exec_id) ON C.cust_adv_id =
P.cust_adv_id) ON E.ee_id = P.ee_id) ON PM.proj_mgr_id = P.proj_mgr_id
WHERE (((F.DateID)=(SELECT Max(X.DateID) FROM funding_history AS X Where
X.DateID <= Forms!frmRptQuery!Combo0 AND X.proj_exec_id=F.proj_exec_id
)));


My Attempt at adding the criteria of actual completion date being <= the
next DateReport from the tblDateSubmitted

SELECT PM.proj_mgr_branch AS [Proj Mgr Code], P.project_title AS Project,
PM.proj_mgr_name AS [Proj Mgr Name], P.proj_risk_level AS [Project Risk
Level], C.cust_adv_name AS [Customer Advocate], E.ee_description AS
[Enterprise Executive (EE)], F.planned_funding AS [Funding to spend in
FY07($K) (From Carryover & New Orders)], F.bcwp AS [BCWP ($K)], F.bcws AS
[BCWS ($K)], F.acwp AS [ACWP ($K)], IIf([bcwp]<>0 And Not
IsNull([bcwp]),CDbl([bcwp])/CDbl([bcwp])) AS SPI, IIf([bcwp]<>0 And Not
IsNull([bcwp]),CDbl([bcwp])/CDbl([acwp])) AS CPI, F.method_of_calculation AS
[Method of Calculation], F.comments AS Comments, F.spi_narrative AS [SPI
Narrative], F.cpi_narrative AS [CPI Narrative], F.carryover_funds AS
[Estimated Carry-over ($k)], F.DateID, tblDateSubmitted.DateReport,
P.actual_complete_date
FROM project_manager AS PM INNER JOIN (enterprise_executive AS E INNER JOIN
(customer_advocate AS C INNER JOIN ((project_execution_metrics AS P INNER
JOIN funding_history AS F ON P.proj_exec_id = F.proj_exec_id) LEFT JOIN
tblDateSubmitted ON F.DateID = tblDateSubmitted.DateReport) ON C.cust_adv_id
= P.cust_adv_id) ON E.ee_id = P.ee_id) ON PM.proj_mgr_id = P.proj_mgr_id
WHERE (((F.DateID)=(SELECT Max(X.DateID) FROM funding_history AS X Where
X.DateID <= Forms!frmRptQuery!Combo0 AND X.proj_exec_id=F.proj_exec_id
AND p.actual_complete_date is Null))) OR
(((P.actual_complete_date)<=[tblDateSubmitted]![DateReport]+1));

thanks in advance for your help