Accessing Query Criteria in a Report (when no data is returned)

[CGM3]

I'm trying to figure out how to access, in a report, the values given to the criteria in the underlying query when no records are found. I've searched the forum to see if a similar case has come up before, with no success.

Here's the situation:



I have a query on an Access 2003 table with a DueDate field that has the Criteria field: Between [Start Due Date] and [End Due Date]. When the query is run, it prompts the user for [Start Due Date] and [End Due Date] and only returns those records that have a DueDate falling within that range. (Yes, I know this is basic stuff, I just want to be thorough. And I may be making a basic mistake I don't even comprehend.)

In the report that uses this query as its source, I have a text field in the header with this formula as data source--

="Documents with Due Date Between " & [Start Due Date] & " and " & [End Due Date]

--which thereby shows the date range entered by the user, a handy reference if the report is printed or exported to file and distributed.

My problem occurs when no data is returned for a given date range, in which case the text field comes out as #Error. As a stopgap measure, I revised the formula to read--

=IIf(IsError([Start Due Date]),"No Documents found for given Due Date range","Documents with Due Date Between " & [Start Due Date] & " and " & [End Due Date])

--but that doesn't tell anyone WHAT date range returned no records.

So the question I have is, is it possible to somehow access, in the report, the values given criteria in the underlying query when no records are returned by the query? Presumably, they're in there somewhere, since they were used to compare with the DueDate field in the table, but is there any way we can show them in the report?

I have been told of a work-around, but it involves considerable revision to my MS Access form, as well as all queries and reports with criteria, and introduces an extra level of administration to the whole report operation wherein the criteria are entered in text fields on the form, which applies the criteria to the query when opening the report, which refers to those text fields on the form for the criteria values. So I’m hoping to find some way to “winkle out” the desired values from the query.

[RuralGuy]

The suggestion you were given is the one I was going to give.

[CGM3]

The suggestion you were given is the one I was going to give.

And it may be what I wind up using, but like I said, it seems that there should be SOME way to reference the values given the query's criteria even when the query returns no records. They have to be in there somewhere, to compare with the data in the relevant field or fields; the problem is, how do we get to them?

[dcrake]

There is a sample database, with documentation on how to handle this type of situation. Basically you need to be using public variables. Because the criteria is collected at query level you cannot detemine the values entered by the user. If you had a form that obtained the range and used these values to filter your query you could use the values elsewhere.

David

[CGM3]

That's the same answer I've gotten from two other sources, put my criteria in fields on the form and revise the query and the report to refer to those fields for the criteria values. Apparently, if there is a way to access the criteria values in the query itself when no data is returned, it is an arcane secret known only to the innermost circle of Microsoft developers.

So, technically, this problem has been solved.

[dcrake]

I don't know if you took the time to look at the sample database I referred to it would go a long way to answer your questions. Also in a report there is an event

Private Sub Report_NoData(Cancel As Integer)
End Sub

You can use this to populate /revise a caption in your report header/detail section that displays a message say that no records were found for the specified range.

David

[CGM3]

Actually, as I indicated in my original post, the report header uses the IIf() and IsError() functions to compensate for the no-data-found situation.