How do I creat a Date Range to open Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Alvin Smith - 26 Feb 2005 13:45 GMT

I have a report named ( Pay Hours ). How can I open this report based on
start date and end date. Thanks in Advance

Reply to this Message

Douglas J. Steele - 26 Feb 2005 13:53 GMT

You can add parameters to the query that the report's based on that'll
prompt you for the dates, or you can create a form where you supply the
dates before you call the report (again, the query would point to the form
field as parameters), or you can put code in the report's Open event that
prompts for the dates, and then set a filter for your report.

Post back if you need help with any of those solutions (make sure you
indicate which one you want to use...)

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have a report named ( Pay Hours ). How can I open this report based on
> start date and end date. Thanks in Advance

Reply to this Message

Duane Hookom - 26 Feb 2005 17:44 GMT

I much prefer the form method that Doug mentions. This page
http://www.fontstuff.com/access/acctut08.htm has some examples of how to
implement this. Form controls are much better than parameter prompts since:
-You can see all criteria values at one time
-Criteria values can be re-used without re-keying
-You can check for integrity
-You can use controls like combo and list boxes, option groups...
-You can set defaults
-You can run code in the form
-You can use input masks
-(other stuff)

Signature

Duane Hookom
MS Access MVP

> You can add parameters to the query that the report's based on that'll
> prompt you for the dates, or you can create a form where you supply the
[quoted text clipped - 7 lines]
>>I have a report named ( Pay Hours ). How can I open this report based on
>> start date and end date. Thanks in Advance

Reply to this Message

Alvin Smith - 01 Mar 2005 21:35 GMT

My Query is working great. But! I have created a form called Form1 which has
two unbound text boxes. Where in the Query do I call this form for the input?
Do I put
WHERE (((Forms!Form1.BillingDate) Between [BeginingDate] And [EndingDate]))
in criteria of the BillingDate? If so it gives me this Error;
undefined function 'Where' in expression.
Thanks for the help!

> I much prefer the form method that Doug mentions. This page
> http://www.fontstuff.com/access/acctut08.htm has some examples of how to
[quoted text clipped - 19 lines]
> >>I have a report named ( Pay Hours ). How can I open this report based on
> >> start date and end date. Thanks in Advance

Reply to this Message

Douglas J. Steele - 01 Mar 2005 22:43 GMT

I would have expected that Form1 would have text boxes txtBeginningDate and
txtEndingDate on it.

If you're working with your query in the graphical designed, you'd put
Between CDate(Forms!Form1.txtBeginningDate) And
CDate(Forms!Form1.txtEndingDate) in the criteria row under the BillingDate
field.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> My Query is working great. But! I have created a form called Form1 which
> has
[quoted text clipped - 34 lines]
>> >>on
>> >> start date and end date. Thanks in Advance

Reply to this Message

Patrick Stansbury - 15 Sep 2005 20:24 GMT

Please explain how to add parameters to a report which will prompt me for
dates when the report is opened. Read everything - guess I am a little slow.
Keep it as simple as possible please!

Thanks!

> You can add parameters to the query that the report's based on that'll
> prompt you for the dates, or you can create a form where you supply the
[quoted text clipped - 7 lines]
> >I have a report named ( Pay Hours ). How can I open this report based on
> > start date and end date. Thanks in Advance

Reply to this Message

Vincent Johns - 15 Sep 2005 22:22 GMT

> Please explain how to add parameters to a report which will prompt me for
> dates when the report is opened. Read everything - guess I am a little slow.
> Keep it as simple as possible please!
>
> Thanks!

In the Query which the Report uses as its data source, add a reference
to some field that you did not define anywhere. For example, suppose
your underlying Table contains these data:

[T_Transactions]

Customer Servicing date amount
Name branch
-------- --------- ----- ------
Jim c 9/13 $15.00
Jim c 9/13 $18.00
Mary c 9/10 $25.00

Define a Query thus:

SELECT T_Transactions.[Customer Name],
T_Transactions.[Servicing branch],
T_Transactions.date, T_Transactions.amount
FROM T_Transactions
WHERE (((T_Transactions.date)=[MyDate]));

and run it; a message box will pop up asking you for a value for
[MyDate]. If you enter "9/13", you should get this:

Customer Servicing date amount
Name branch
-------- --------- ----- ------
Jim c 9/13 $15.00
Jim c 9/13 $18.00

Be aware that there are fancier ways to do this, such as defining your
own Text Box, but this should do what you need for now.

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

>>You can add parameters to the query that the report's based on that'll
>>prompt you for the dates, or you can create a form where you supply the
[quoted text clipped - 7 lines]
>>>I have a report named ( Pay Hours ). How can I open this report based on
>>>start date and end date. Thanks in Advance

Reply to this Message

Patrick Stansbury - 15 Sep 2005 22:37 GMT

Thaanks Vincent,

But I am a little too inexperienced to understand what you wrote... What I
have is a sales report that includes records like INVOICE DATE and CLIENT and
AMOUNT and AD SIZE and more. The report works FINE - but it includes every
record in my database. What I want to happen is that the report will ask for
a date range - using the dates in my INVOICE DATE field (09-15-2005 is how it
is expressed) to generate the report. I need to be able to do a single day -
a week or a entire month. It seems it should be easy - but I have been
sitting here for hours and can not figure out how to do it!

Can you offer help?

Thanks!

> > Please explain how to add parameters to a report which will prompt me for
> > dates when the report is opened. Read everything - guess I am a little slow.
[quoted text clipped - 49 lines]
> >>>I have a report named ( Pay Hours ). How can I open this report based on
> >>>start date and end date. Thanks in Advance

Reply to this Message

Vincent Johns - 17 Sep 2005 16:03 GMT

> Thaanks Vincent,
>
[quoted text clipped - 10 lines]
>
> Thanks!

OK, if you want to use a date range, then you can use a Query like the
one I suggested, except with two dates, something like this:

... WHERE (((T_Transactions.date)>=[StartDate])
AND ((T_Transactions.date)<=[EndDate]));

If you know that the value of your parameter [EndDate] is always going
to be yesterday, or last Saturday, then you can compute that with a
function and not have the user type in a date. The query would then
contain something like this, in which Now() is the function referring to
the current moment, though you'd probably want to use some variation on
that:

... WHERE (((T_Transactions.date)>=[StartDate])
AND ((T_Transactions.date)<=Now()));

If you've tried using the Query I suggested earlier, did that work? If
not, what happened when you tried using it?

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

>>>Please explain how to add parameters to a report which will prompt me for
>>>dates when the report is opened. Read everything - guess I am a little slow.
[quoted text clipped - 37 lines]
>> -- Vincent Johns <vjohns@alumni.caltech.edu>
>> Please feel free to quote anything I say here.

Reply to this Message

Patrick Stansbury - 15 Sep 2005 23:37 GMT

Mr. Steele, in beginners terms - how do I add parameters to a report that
will prompt it to ask for dates? I added the parameter - it asked for the
dates - but the report was unchanged. It still listed all data rather than a
specific date. One expression in my database is INVOICE DATE. I want to be
able to generate areport by day, week or month...

Thanks for your consideration!

> You can add parameters to the query that the report's based on that'll
> prompt you for the dates, or you can create a form where you supply the
[quoted text clipped - 7 lines]
> >I have a report named ( Pay Hours ). How can I open this report based on
> > start date and end date. Thanks in Advance

Reply to this Message

John Vinson - 16 Sep 2005 01:22 GMT

>Mr. Steele, in beginners terms - how do I add parameters to a report that
>will prompt it to ask for dates? I added the parameter - it asked for the
>dates - but the report was unchanged. It still listed all data rather than a
>specific date. One expression in my database is INVOICE DATE. I want to be
>able to generate areport by day, week or month...

You don't add parameters to a Report.

You add parameters to a Query, and *base the Report on the Query*.

It sounds like the Report is based on your table. Open the report in
design view, and - if you have the query Douglas suggested - change
the Record Source property of the Report from the name of your table
to the name of that query.

John W. Vinson[MVP]

Reply to this Message