How do I creat a Date Range to open Report

[Guest]

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

 

[Douglas J. Steele]

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...)

 

[Duane Hookom]

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)

 

[Guest]

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!

 

[Douglas J. Steele]

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.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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
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)

 

[Guest]

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!

 

[Vincent Johns]

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 <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

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.
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 <[email protected]>
Please feel free to quote anything I say here.

:

 

[Guest]

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!

 

[John Vinson]

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]

 

[Vincent Johns]

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!

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 <[email protected]>
Please feel free to quote anything I say here.

:

Patrick Stansbury wrote:

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 <[email protected]>
Please feel free to quote anything I say here.