Display Date Ranges in Access

[HeidiE]

Paul Bricker,

I'm a beginner in Access and have the same question as jer007 "I am running a report based on a query which requires the user to input a date range (Criteria: Between [start]AND [END]). This report displays the information within that range and sums the financial information. I am having trouble with two items.

1. How can I display the date range selected by the user on the report?"

Only my query's criteria is as follows (Between #7/1/2004# and 12/31/2004#) Can I display this type of date range in a report?

 

[THWatson]

Heidi
I see your question is addressed to Paul, but I'll jump in here.

I gather that in your date column in the query you have criteria Between [start] And [END]. Then in your report header put an unbound text box. The control source for the text box would be...
="Between " & [start] & " And " & [END]
You can put anything you like in the quotes. Something such as
"This report covers from " & [start] & " to " & [END]

But if you want to try a different, and in the long run better, approach than putting criteria in your query column, do this...
1. Make an unbound form. Let's call it frmDateSelector. Put on it two text boxes, one named txtStartDate, the other named txtEndDate
2. In the criteria row for your date column in the query, put
Between Forms!frmDateSelector!txtStartDate And Forms!frmDateSelector!txtEndDate
3. Then in your report header, put
"This report covers from " & Forms!frmDateSelector!txtStartDate & " to " & Forms!frmDateSelector!txtEndDate

To answer your second question, it doesn't matter the date range. It can be as long or as short as you like.

Tom

 

[HeidiE]

Tom,

I run these reports quarterly, semi-annually, and annually. There are 54 reports that pull from 16 different queries. My date ranges change each quarter and I would like my report header to automatically update according to the date range I have entered in each of the queries. All of the 16 queries will have the same date range criteria e.g. (Between #10/1/2004# and #12/31/2004#). So, in my report, will the undbound text box with the control source ="Between " & [start] & " And " & [END] ="Between " & [start] & " And " & [END] achieve what I’m looking for?

 

[THWatson]

Heidi
If what you are showing here (Between #10/1/2004# and #12/31/2004#) is what you have in the criteria column in the queries, then what I gave you won't achieve what you are looking for. But going in an entering those date values every time you want to run report is putting yourself to too much work.

That's why I recommended running stuff from a form. And you could run all 54 report from the same form.

1. Make your unbound form, with the two text boxes, as I indicated in my first reply.
2. In each of the queries put the exact same criteria Between Forms!frmDateSelector!txtStartDate And Forms!frmDateSelector!txtEndDate
3. Put this exact same thing in your various report headers.
4. On the frmDateSelector form, use the wizard to put a command button to run one report. One of the lines in the code behind the command button will be a DoCmd line. Add additional DoCmd lines for each of the reports you want to run. (or you could put additional command buttons on the form, one for each report, or one to do the quarterly collection of reports and then another for the semi-annual collection, and so on).

If you set it up this way, you save yourself a ton of work.

Tom

 

[PaulBricker]

Heidi, I saw you post in the other thread and gave you the same answer that Tom originally gave you. I would keep the rest of your replys and questions in this thread so things don't get confused. Tom's suggestions are very good and you might consider them, but if you want to use your system the way it is, then here is what I would do. In each query, I would add an expression in a new column that said
MyDate:"Between 10/1/04 and 12/31/04"

This will give you a whole column of the parameter dates. In your report, you just set the Control Source for your textbox to
MyDate

That will give you the dates in the report you are looking for. You can change the expression in MyDate to anything you want like
MyDate:"This report is for the 2nd quarter FY2005"

Whatever works for you.

Paul

 

[HeidiE]

Tom and Paul,

Thank you both for your suggestions. I believe adding the MyDate expression to the queries might be the best option for me. When I tried Tom's "Date Selector Form" method it seemed to affect some calculated text boxes in my report. Some text boxes worked correctly but the calculated text boxes all read "error" and the report header read "Name?"

Thanks for your help.

Heidi

 

[THWatson]

Heidi
From what you experienced, it's clear that the approach I suggested takes more rebuilding.

Paul's approach is clean and neat.

Tom