I have a report that pulls information from a query. The query has a date field and I’m using a “Between” statement to restrict the results to a user input date range. The query criteria field reads as follows: Between [Start Date] AND [End Date]. When I run the query everything works fine (the user is prompted for a date range and the appropriate results are returned)
The problem is when the report that is bound to this query is opened I can’t seem to use any aggregate functions to manipulate the data in the query. Let me give an example: The query has two fields one named Minutes and the other named LineNumber. When I open the report I have a textbox with the control source set as follows: =DSum(“[Minutes]”, “QueryName”, “[LineNumber] = ‘2’”). When the report opens, the user is prompted for a date range, but all the textboxes return a #Error.
I have noted the following:
The control source statement shown above works in any report that does not prompt the user for the date range (for example, if I use >= #11/1/04# in the query criteria…it works or if I use Between #11/1/04# AND #11/9/04# as the criteria... it works) Anything that avoids prompting the user seems to work.
If I don’t use the report to open the query everything works fine. (you can click on the query, get prompted for the date ranges and get the right results)
It seems to me that this method should work and it’s beginning to drive me crazy! Does anyone have any ideas what the heck is wrong?