Access Data Project Paramaters in sub queries

[oihjk]

Hello Everyone,
It has been a while since I have posted here.
I am working on a query to show information from various tables. It's purpose is to show the costs for each job.

I am using an sql backend for an Access Data Project. I need to have parameters that point to a control on a form to filter the data by end date. I could do this in an mdb file by using [Forms]![frmName]![cntrlName] in the where clause, but this is not acceptable in an ADP file (at that is what I have experienced). I could also use a stored procedure, however since I need to filter multiple sub queries that does not seem to be an option.

Here is what I need to show in one view:
1. Show all the jobs where status <> "Invoiced" and status <> "Cancelled"


For each of these jobs I need to show the following:
2. Show the Sum of the Cost of all POs where Type <> "Sub Contractor" AND OrderDate < [Forms]![frmName]![cntrlName]

3. Show the Sum of the Cost of all POs where Type = "Sub Contractor" AND OrderDate < [Forms]![frmName]![cntrlName]

4. Show the sum of the Labor Cost of all Timesheets where CostCode = 3010-03 and WorkDate < [Forms]![frmName]![cntrlName]

5. Show the sum of the Labor Cost of all Timesheets where CostCode = 3010-04 and WorkDate < [Forms]![frmName]![cntrlName]

Any ideas?

Thanks,
Eric

P.S. I have attached an excel spread sheet showing a sample of the how I want the query results to look.