In an Access 2007 application, I have a report that has two sub reports. The parent report is opened from code and is filtered down to showing data about 1 project and therefore has 1 ProjectID. I have that ProjectID tied to the ProjectID of the first subreport to filter it's data down to show only that data corresponding to the project of the parent report. The second report however is giving me problems as it is a lot more complex of a query. Below is shown a query that I have built in Access query builder that I have finally gotten to show the data correctly for 1 project. Notice the WHERE clause...
D IS NULL OR tbContractProject.ProjectI
D = 4
I want to use this query to build a report that will act as my second subreport and I need the 4 in the WHERE clause to change to the ProjectID of the project loaded into the parent report. I'm not sure how to go about doing this.
Any help would be greatly appreciated.
I need the 4 to be changed to the ProjectID of the parent report
SELECT tbExpenditureType.ExpenditureType, SUM(IIf(IsNull(tbExpenditureLedger.Amount),0,tbExpenditureLedger.Amount)) AS Amount
FROM ((tbExpenditureType LEFT JOIN tbFundingSourceFundingTypeExpenditureType ON tbExpenditureType.ExpenditureTypeID = tbFundingSourceFundingTypeExpenditureType.ExpenditureTypeID) LEFT JOIN tbExpenditureLedger ON tbFundingSourceFundingTypeExpenditureType.FundingSourceFundingTypeExpenditureTypeID = tbExpenditureLedger.FundingSourceFundingTypeExpenditureTypeID) LEFT JOIN tbContractProject ON tbExpenditureLedger.ContractProjectID = tbContractProject.ContractProjectID WHERE tbContractProject.ProjectID IS NULL OR tbContractProject.ProjectID = 4
GROUP BY tbExpenditureType.ExpenditureType