Putting the date used in a Query into a Header Field of a Report

[Tom Evans]

I need help please. I am designing a Goods Dispatched Report which is based on a Query which retrieves the information from 2 Tables based on a date period e.g. 27 September to 26 October.


The Query on the Date field looks like this “Between [Enter Start Date] and [Enter End Date]”.
My problem is that I want the “Start Date” and the “End Date” to appear in the Header section of the report. Can this be done? If so, how?

[madpiet]

I need help please. I am designing a Goods Dispatched Report which is based on a Query which retrieves the information from 2 Tables based on a date period e.g. 27 September to 26 October.
The Query on the Date field looks like this “Between [Enter Start Date] and [Enter End Date]”.
My problem is that I want the “Start Date” and the “End Date” to appear in the Header section of the report. Can this be done? If so, how?

Remove the parameters from the query, and then move the filter to the Open event of the report. Then create an unbound form and put two unbound controls on it, one for each date. (so format them as dates). Then you can just add a button to your form that opens the report,

DoCmd.OpenReport "someReport, Between Forms!frmUnboundParamForm![StartDate] AND Forms!frmUnboundParamForm![EndDate]

Oh, right... putting the values in the header... two unbound textboxes one =Forms!frmUnboundParamForm![StartDate] and the other points to the other parameter.

[Tom Evans]

Remove the parameters from the query, and then move the filter to the Open event of the report. Then create an unbound form and put two unbound controls on it, one for each date. (so format them as dates). Then you can just add a button to your form that opens the report,

DoCmd.OpenReport "someReport, Between Forms!frmUnboundParamForm![StartDate] AND Forms!frmUnboundParamForm![EndDate]

Oh, right... putting the values in the header... two unbound textboxes one =Forms!frmUnboundParamForm![StartDate] and the other points to the other parameter.

Thanks ever so much. Will try it today.

[Tom Evans]

Thanks ever so much. Will try it today.

Hello Madpiet. Please explain a little bit more what "moving the filter to the Open event of the Report" means.
Thanks

[madpiet]

Tom,
first the easy part: showing the date range of the filter on the report:

Put a text box in the header of the report.
Control Source of the textbox:

="FROM: " & Format([Forms]![frmFilterCostcoData]![cboStartDate],"yyyy-mmm-dd") & " TO: " & Format([Forms]![frmFilterCostcoData]![cboEndDate],"yyyy-mmm-dd")

When the report runs, it will grab the values you pass from the frmFilterCostcoData form controls and the report will only contain the records that fall between the two dates, cboStartDate and cboEndDate.

Filtering the report...

Instead of a hardcoded filter in the query that "gathers" the data for your report, you pass that same WHERE clause (minus the WHERE keyword) in the Open event of the report.

Both queries and reports have filters (basically a WHERE clause, except in a report, you leave off the "WHERE" keyword). So for filters you *always* want applied to the report, you usually put those in the WHERE clause of the query you base the report on. If you want to apply additional filters to the report when you open the report, you can also pass a filter to the report, which filters the report data source a second time (the first is in the query's WHERE clause).

The most flexible way to use a report is to leave the filtering out of the report's data source (the query the report is based on) and then pass a filter in the report's open event in VBA, like this:

Private Sub Command5_Click()    Dim strWhere As String
    strWhere = "[InvoiceDate] >= #" & Me.cboStartDate & "# AND [InvoiceDate] <= #" & Me.cboEndDate & "#"
    
    DoCmd.OpenReport "dbo_CostcoData", acViewReport, , strWhere, acWindowNormal
End Sub

I could add other filters, but basically what's happening is that the report is evaluating the contents of the two comboboxes cboStartDate and cboEndDate and passing that filter to the report when it opens (that's what "strWhere" is doing in the OpenReport command).

You don't have to use just one or the other. If you always want the report to have a given static filter, you could include that in the WHERE clause of the query the report is based on, and then add more filters in the code, and the two sets of filters are just basically ANDed together.

Hope that answers your question.

[Micron]

I might use one unbound control and concatenate, as in
="For the period between " & [StartDate] " and " & [EndDate]. That way, no matter the physical width of the date values you'll always end up with a well formed sentence. Otherwise, you'll have varying space width between your header words and dates. I used my own field names to illustrate that spaces in field names should be avoided.

[madpiet]

(Thought I did that... sorry...was a bit of an afterthought).

[Micron]

If you did I missed it. I did see this part

Then create an unbound form and put two unbound controls on it

so I wasn't looking for a different suggestion about that.

[Tom Evans]

Thanks Madpiet. I shouldn't have any further problems as you have explained it very well.
Have a great day!

[madpiet]

You're welcome. Post back if you get stuck. There are lots of folks here who can help you out.