Date Range on Report

carrietm
2StarLounger
Posts: 110
Joined: 08 Mar 2011, 13:18

Date Range on Report

Post by carrietm »

I need to have the date range that was used for the data collection appear on my report.

Originally I had a prompt on my query asking for the start and end date but that was a nightmare because too many additional queries were based off of it. As a work around, I have a set date range in a single query which unfortunately, the end user will have to carefully adjust in design view. (Scary, I know!)

I'm thinking there must be a way to put two text boxes on my report and prompt the user to enter a start date and then an end date so they know what date range was used for the report. My fear with that is, they may forget to adjust the date range on the query in which case the correct date range would not be declared.

My other thought was, it would be great if the actual date range set in the query would appear on the report but unfortunately, I don't know how to write the code for either one.

Any guidance you can offer will be appreciated.

rptStaffCompliance_v2
qryUnitCompliance_1
DateCollected Between #01/01/2011# And #12/31/2020#

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Date Range on Report

Post by HansV »

I'd create a form frmParameters with two text boxes txtStart and txtEnd (both with Format set to a date format) and a command button cmdOpenReport.
Create an On Click event procedure for the command button (or use the command button wizard):

Code: Select all

Private Sub cmdOpenReport_Click()
    On Error GoTo ErrHandler
    DoCmd.OpenReport ReportName:="rptStaffCompliance_v2", View:=acViewPreview
    Exit Sub
ErrHandler:
    If Err = 2501 Then
        ' Report canceled, we can ignore this
    Else
        MsgBox Err.Description, vbExclamation
    End If
End Sub
Close and save the form.

Open the query in design view.
Change the date range condition to

Between [Forms]![frmParameters]![txtStart] And [Forms]![frmParameters]![txtEnd]

Click Query | Parameters (Access 2003 or earlier) or click Parameters on the Design tab of the ribbon (Access 2007 or later).
Enter [Forms]![frmParameters]![txtStart] and select Date/Time as data type.
In the next row, enter [Forms]![frmParameters]![txtEnd] and select Date/Time as data type.
Close and save the query.

On your report, place a text box in the report header or footer with control source

=[Forms]![frmParameters]![txtStart]

and another with control source

=[Forms]![frmParameters]![txtEnd]

Set the Format of both text boxes to your preferred date format.
Best wishes,
Hans

User avatar
Wendell
4StarLounger
Posts: 482
Joined: 24 Jan 2010, 15:02
Location: Colorado, USA

Re: Date Range on Report

Post by Wendell »

As Hans suggests, the most common approach to this problem is to open a form which has text boxes for the start date and the end date. Another alternative to putting the criteria into the query as he has shown, is to use the "Where" parameter of the DoCmd.OpenReport statement. That way the query can be used for many different reports, and the criteria is specified using different forms.
Wendell
You can't see the view if you don't climb the mountain!

carrietm
2StarLounger
Posts: 110
Joined: 08 Mar 2011, 13:18

Re: Date Range on Report

Post by carrietm »

I'll give it a whirl. Thank you for the support.

:0)

carrietm
2StarLounger
Posts: 110
Joined: 08 Mar 2011, 13:18

Re: Date Range on Report

Post by carrietm »

This may be more complicated than I thought. I went through the instructions step by step but I may have messed it up. It prompted me for the date range 10 times before I finally gave up. I've worked to the best of my abilities but have used the same query for multiple other queries.

I'm using [qryUnitCompliance_1] the one with the date range for:
[qryUnitCompliance_2Den] a group by query used to get the unit denominator
[qryUnitCompliance_3Yes] a group by query used to get the numerator or number of times they were compliant.
[qryUnitCompliance_final] is used to bring the numbers together and used for the total subquery on [srptUnitCompliance_final]

[qryUnitCompliance_1] (original one with the date range) is also used in the same way to determine compliance at a staff position level.
[qryStaffOnly_2Den]
[qryStaffOnly_3Yes]
[qryStaffOnly_final] used as above and for the control source on [rptStaffCompliance_v2] AND [rptStaffOnly_final]

I will be using multiple reports as Wendell has mentioned. Could you please tell me what to use for the DoCmd or have I made it too complicated with the multiple queries? As it's set up the date range is just in [qryUnitCompliance_1].

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Date Range on Report

Post by HansV »

If you have set up the form and changed the criteria to

Between [Forms]![frmParameters]![txtStart] And [Forms]![frmParameters]![txtEnd]

(or similar), you must open the form and fill in the dates before opening the report. If you still get parameter prompts, that probably indicates that there is a spelling error somewhere, so check carefully.
Best wishes,
Hans

carrietm
2StarLounger
Posts: 110
Joined: 08 Mar 2011, 13:18

Re: Date Range on Report

Post by carrietm »

I see! I didn't open the form first!

Thank you.

carrietm
2StarLounger
Posts: 110
Joined: 08 Mar 2011, 13:18

Re: Date Range on Report

Post by carrietm »

Wow. That is so great! I can't thank you enough.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Date Range on Report

Post by HansV »

You're welcome!
Best wishes,
Hans