Creating a Monthly Report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SLA - 06 Aug 2004 20:05 GMT

I need to report data for each month from an Access database. I would like to
set up the report so that I do not have to change the query each month. I
would like the business user to run the report for a specified month without
having to deal with query or with me. I have set up a query using the
STARTDATETIME field ( (DatePart("m",[StatsModuleLaunch]![StartDateTime]))=7)
to find the appropriate records . Right now, I go and change the query and
then run the report based on that query. How do I set up a prompt to change
the report ? It would be easier for business user to be able to answer a
prompt on a report vs. trying to work with queries. Also How do I
"automatically" change the Report Header so that it displays the month ie.,
Monthly Report for June 2004?

Reply to this Message

- 06 Aug 2004 20:33 GMT

Try this to prompt your users...
Where you show 7 in your example (for July, I presume),
substitute the prompt string [Enter month number desired]
This will not be year sensitive, so don't mix records
from different years.

In the report title area insert a text box for the
month space containing the expression =MONTH
(STARTDATETIME), and format the box as MMMM). This should
work provided all the records passed to the report are
from the same month (as they should be).

Hope this works for you.

>-----Original Message-----
>I need to report data for each month from an Access database. I would like to
[quoted text clipped - 9 lines]
>Monthly Report for June 2004?
>.

Reply to this Message

- 06 Aug 2004 20:44 GMT

Sorry - I goofed a bit. For the text box in the title
area use =STARTMONTHDATE, not the MONTH() Expression.

>-----Original Message-----
>Try this to prompt your users...
[quoted text clipped - 36 lines]
>>
>.

Reply to this Message

Duane Hookom - 06 Aug 2004 20:33 GMT

I would use a form with a text box that allows the user to select the month.
You could default the text box to either the current month or past month.
Then your report's recordsource would have a criteria like:
Month([DateField]) = Forms!frmMonthSelect!txtMonth
This will vary depending on if your date field contains records for more
than one year.

Signature

Duane Hookom
MS Access MVP

> I need to report data for each month from an Access database. I would like to
> set up the report so that I do not have to change the query each month. I
[quoted text clipped - 7 lines]
> "automatically" change the Report Header so that it displays the month ie.,
> Monthly Report for June 2004?

Reply to this Message

YL - 21 Jul 2005 17:56 GMT

It was really really helpful~ Thank you.

> I would use a form with a text box that allows the user to select the month.
> You could default the text box to either the current month or past month.
[quoted text clipped - 19 lines]
> ie.,
> > Monthly Report for June 2004?

Reply to this Message