Generating Monthly Report in MS ACCESS (1 Viewer)

Hi,

I am creating a database in MS ACCESS for data-entry of records, where the system should track the date of record entry. I am using the function date() to track the user entry date, which automatically saves the system date in short date format (12/24/2003) as soon as the user saves the record. I need help for generating of reports, I have a form where the user is given the check box to select the month for which he needs to pull up the details; suppose the user wants the details of the records entered in the month December, the system should display all the records entered between 12/01/2003 to 12/31/2003 in a report. Please help if this is possible in MS ACCESS.

Sahil,

Yes, its possible.

One option is to bring up a dialog box and have the user enter
the Start and End dates. Then a query can display the related
records on a form/report.

If you only want them to enter "December", then you will have
to use some VBA functions like DatePart to find the matching
records.

Wayne

Generating Reports in MS ACCESS

Dear WayneRyan,

Thanks for your help. The first option of a dialog box for user entry of start and end dates would be fine. But how do I write a dynamic query for generating the report within the dates specified by the user. I mean the report I use would be based on a query, and in the query how do I track what the user is entering as start and end date and also suggest how a dialog box would be used in this scenario.

A quick and dirty way to do it (that you don't have to build a form for) is in the date field of your query type Between [enter report start date] and [enter report end date].
When the query runs a box will pop up prompting you for the dates to enter. If you have a form with the start and end dates, replace what's in the brackets with the name of the form fields.

You could also do a custom field using format() in the query that pulls out the month and prompt for just the month.

Dear Nakin,

The problem is I have always been using static reports based on a query, like say "Select entry_date from Batch", but this time I want to generate a report based on a user entry, so I am not able to figure out how do I go about it. I am unable to understand how do I track a user request while generating a report in Access. Can you please help me out in a detailed manner?

Am I understanding you correctly in that you want to track (record) who has requested the report for what dates?
I may be way off but I'm a little thick these days due to a head cold.

If you don't need to record it, look in the help files index under parameter_query. The first three entries will tell you exactly what you need to know about making a dynamic query that either prompts from the query itself or takes the parameters from a form. You will be able to use your present report query with just a tiny modification.

pm me if you want a sample db

To create a dynamic report based on time, you need to do these:
1- in Module editor, create two functions
1.1 public function BegDate()
BegDate = inputBox("Enter your Beginning Date ....")
End Function
1.2 public function EndDate()
EndDate = inputBox("Enter your End Date...")
End Function

2- Create your query including the date field. On the Criteria space, I hope you are using QBE, add " Between BegDate() And EndDate()". Now run this query. This Query will prompt you to enter Beginning date and end date and will display all records within this period.


3- Go create report according to whatever fromat you want using this query as datasource. On a different form or switchboard creat a button for users to preview the report. When a button is click, users will be prompted to enter beginning date and end date. The good thing about this is you do not have to only request a report during a month period, instead you could print a report for two, three or more days.

I hope that helps!

Thanks,

Domach

What's the point of creating a Function for this? You might just as well put Between [EnterStartDate] And [EnterEndDate] directly in the criteria.
Most would prefer to use a form or CalControl to enter dates.

Thanks for your replies guys!
It worked using Calendar control and query between [Enterstartdate] and [Enterenddate]

Once again thanks for your help