Set a subquery variable before opening a report

[projection]

Hi,

I have an interesting problem where I want to create a report using a select statement having a subquery union of several tables (partial query below). The sub union query has a variable (sPeriod) that will be set to the Period ending Date specified on the form before the user clicks view report.

Instead of coding this really long query in VB, how can I use this query as the recordsource and pass the value to the report and have it set the sPeriod variable in the subquery to the value? The where condition on openReport command only works with top level queries, at least that was my assumption as it didn't work when I tried. I cannot have the dateEntered as part of the top level query.

Another issue with coding a really long query in VB and setting it to the recordsource before opening the report is that there is a length limitation on the recordsource property. I solved this problem by creating a temporary table and setting the report to the temporary table, but I would rather not do this since I am not the only one creating reports.

Here is part of the query:

SELECT *
FROM (select id, sum(cost) as total_cost from (
                           select matterid as ID, Cost1+Cost2+Cost3+Cost4+Cost5 as COST  
                           from tblActual  
                           where dateentered <= cdate(sPeriod)
                           union all 
                           select matterid as ID, Cost1+Cost2+Cost3+Cost4+Cost5 as COST 
                           from tblMiscActual  
                           where dateentered <= cdate(sPeriod)
                           union all 
                           select client_matter_ID AS id, Invoice_total as COST 
                           from tblInvoices  
                           where invoice_date <= cdate(sPeriod)
                           union all 
                           select client_matter_ID AS id, Invoice_total as COST
                           from tblInvoices where invoice_date <= cdate(sPeriod)
                 ) group by id
         ) AS cost_by_project;

This is the subquery that is left joined to the project table.

Please let me know if any clarification is necessary.

Thank you in advance for helping me!

Rob

 

[BoxHead]

There are a couple of things to "trip over" with parameters: References & Rules.

I'm not seeing how sPeriod is being set to the value selected/entered on the form.

a variable (sPeriod) that will be set to the Period ending Date specified on the form

The reference should be set to the name of the control on the form along the lines of Form!frmName.textboxName

To get the correct reference, right-click on the criteria cell in the query grid and select "build". A dialog box will open and you can navigate to the form and the control name.

There are some rules (for example in Crosstabs) restricting when you may or may not use run-time parameters. I haven't tried it, but Union queries may well be one of the limitations.

If setting the criteria to reference the Form Controls doesn't solve it, you could create a single record table with a Date data type. eg - tblDateSelect, fldDate

Set your query criteria to be <= DLookup("[fldDate]","tblDateSelect") and use the form to edit that field's value.


HTH







When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[projection]

Thank you for replying BoxHead.

I went ahead and used the createquerydef function to create a temporary query and set that to the recordsource of the report.