Displaying Date Span Report Is Based Upon

[twitchie]

I have a report in Access 2010 whose control source is a query. When the report is launched, the query asks the user for a date span to filter the results with. The user enters the start date in MM/DD/YYYY format in the box and then the same for the end date. The report filters the dates just fine and displays them as desired.

However, my question surrounds displaying the two dates that the user enters. I've have 2 test boxes - one for the lowest date and one for the highest date of the report. However, every time the report runs, those 2 boxes populate with #Error. I have tried =Max([DateOfExpense]) as well as =Min( [qry_ExpensesDateRange]![DateOfExpense] ) . What am I doing wrong here? Many thanks in advance!

 

[plog]

I got the same error as you did when I tried to put that in the header. Maybe someone has a better answer, but here's how I got around it:

I put those boxes in the footer, went into their properties made them both not visible and changed their names to "MinDate" and "MaxDate" respectively. Next I added two new boxes to the header and set their control sources to "MinDate" and "MaxDate" and it worked.

One note--this only displays the minimum and maximum values of the data on the report. That means if your user enters 1/1/2009 and 12/31/2010 and their is no data for the first 3 days of the year, then your report will show 1/4/2009 and 12/31/2010 instead of 1/1/2009 and 12/31/2010 like the user entered.

 

[asgard]

I got the same error as you did when I tried to put that in the header. Maybe someone has a better answer, but here's how I got around it:

I put those boxes in the footer, went into their properties made them both not visible and changed their names to "MinDate" and "MaxDate" respectively. Next I added two new boxes to the header and set their control sources to "MinDate" and "MaxDate" and it worked.

One note--this only displays the minimum and maximum values of the data on the report. That means if your user enters 1/1/2009 and 12/31/2010 and their is no data for the first 3 days of the year, then your report will show 1/4/2009 and 12/31/2010 instead of 1/1/2009 and 12/31/2010 like the user entered.

It seems like putting boxes in the footer is the way to go for now

 

[boblarson]

If you used a form for input you could just refer to it in the text boxes (as long as the form remained open). But you can also do it if you use the EXACT SAME prompt that you used for the query.

So, for example, if you have a parameter prompt in the query underlying the report that goes like this:

Between [Enter Start Date] And [Enter End Date]

you should be able to set your text boxes to:

=[Enter Start Date]


=[Enter End Date]


or even just one text box:

="From " & [Enter Start Date] & " To " & [Enter End Date]

or with a format

="From " & Format([Enter Start Date], "dd mmm yyyy") & " To " & Format([Enter End Date], "dd mmm yyyy")


you just have to make sure you have used the exact same prompt that you used for the query and it should only ask you ONE TIME for it when you open the report.