Hello UA,
I can't seem to get an expression right in a query, for the criteria. I'm trying to draw on the ID values (myfieldID) for records in a subreport. This query will only run when the report is open (perhaps using report values for criteria doesn't work at all??). Here's what I have so far:

[reports].[myReport].[myReportSub].[report]![myfieldID]

Is there some adjustment on the syntax that would make this work? Many thanks in advance!

It works the same for controls on forms: the report or form must be open in order to refer to the controls in a query.

What's the scenario you're trying to address? Since reports show data that exists in tables, can you perhaps query the source table of the report instead?

Hi Doug,
The report has records in a single table, that are records in a subform on a form the report is opened from. The subreport draws from the same table but shows related records (by a common ID number in a 'link' field) that are NOT shown on the subform. The purpose is to show, in the subreport, related records (multiple) of each record, but that are hidden. I use a 'find unmatched' query for the subreport so it only shows 'hidden' records and not the 'main' records. The subreport is in the Detail section of the report, and connected via that 'linked' field.

The result is the report lists out each record on the subform in question, and below each of these records is a list of records that are related but hidden (not on the subform). I've got this part working nicely and it's visually easy to understand. But the last thing I need to do is take the totals (a cost field) of the 'hidden' (i.e. subreport) records and put them on the main report, to be used in other calculations.

I can do this via =[myReportSub].Report!myCostTotal. The problem is there are not always records in the subreport, for a record in the main report's detail, and so the 'myCostTotal' will not have totaled, and so the =[myReportSub].Report!myCostTotal for those records says #name? (because there is no data to transfer from the subreport). If I could get this to default to '0' if there are no records to total that would work but I can't figure out how to do it. So what I was trying to do was just run a simple query, using the subreport records in the criteria, and then total it, and then do a DLookUp from that query to put the total on the main report.

If there's a simpler way to use =[myReportSub].Report!myCostTotal and deal with the fact there may be no data to transfer, that would be nice as well.

Handing situations where there's no data is complicated. (In fact, I wrote an entire article about this for Database Journal, Reporting what's not there)

You should be able to put logic in the report to check whether the subreport has any records, and report 0 in that case (although I have to confess a simple method doesn't occur to me at the moment!) Worst case, you should be able to use VBA to check whether the subreport has records, and set the myCostTotal field to 0 if it doesn't.

I've wrestled with the 'no data' issue on several fronts- never easy for sure.

So if I put in the report detail section's OnFormat something like:

If Me.[myReportSub].report.RecordsetClone.RecordCount = 0 Then
Me.myTransferredCostTotal = 0
Else
Me.myTransferredCostToal =[myReportSub].Report!myCostTotal
End If

I know this doesn't work but is this along the lines of what you mean?

Yeah, that's essentially what I was thinking of.

I finally got it to work by creating a different query (not dependent on the subreport) running unmatched but linked records (and totaled by sum), doing a DLookUp in an unbound totals control of the main report.

To deal with the 'no data' issue I had to put in an IIf(IsNull(DLookUp("mycostsum","myQuery"))=true,0,DLookUp("mycostsum","my query")) for the unbound control's data source. That seems to make it work well. Dealing with the 'no data' issue was the sticking point. Hope this is understandable to anyone else who needs this. Thanks for the help Doug!