Referring to a cell with a date in query criteria

[Historypaul]

I'm having a problem getting a query to filter on a date from the parent forms cell... I want my subreport to return only the results prior to the date shown on the master report. I tried <[Forms].[Master].[RecordDate] and I've tried many variations including the # symbols such as <#[Forms].[Master].[RecordDate]# . I'm sure I'm just missing something simple... Any advice?

[Historypaul]

Maybe it would be easier if I simplify what I'm looking to do... Maybe I'm going down the wrong direction...

Simply put, I have an order form. While printing a report of a current order, I want it to show the sum of the previous orders in the subreport. I just somehow need to return the sum of all orders prior to the date of the current order...

[June7]

Is the Master object a form or a report? You seem to be mixing the references in your narrative.

If the report output is filtered to a single order record, cannot sum all prior order records because they are not in the report RecordSource. Would have to use a DSum domain aggregate function or perhaps a nested subquery as part of the report RecordSource. http://allenbrowne.com/subquery-01.html#YTD

[Historypaul]

Sorry - the master object is a report. I do have it filtered to a single record, so what I've done so far is to create a multiple items subreport inside the main that is a multiple items report with a sum cell at the bottom. I did finally get it to filter to <Date cell on the master form, but now I get an error message of "The search key was not found in any record" each time I open the report. I also get a #Size! error in the cell that refers to the sum cell on the subreport if it is a null. I wrapped the reference in a Nz(Referenced cell,0) with no luck. And I only get the Size! problem in print preview, not report view... weird.

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Historypaul]

Thanks, June... I followed the path you were going with the Dsum. It worked beautifully. I was able to get rid of that entire subreport. I had a brief fight with quotation marks, but the Dsum seems to be working now and even encapsulating it in the Nz function seems to be giving me a zero when the Dsum fails like I had hoped. Just for grins, I included the code below.

I chose to run the Dsum off of a query, because I needed to join two tables in order to have complete data to pull from.

=Nz(DSum("[SumOfScopeCost]","[SubcontractorCOs_Query]","[SubcontractorCOs_Query]![SubOrSupplierID]=[Reports]![SubcontractorCO].[SubOrSupplierID] And [SubcontractorCOs_Query]![ProjectID]=[Reports]![SubcontractorCO].[ProjectID] And [SubcontractorCOs_Query]![CODate]<[Reports]![SubcontractorCO].[CODate]"),0)