Need to make a Weekly Report (2000)

[villalandron]

I need to make a weekly report and i need to know how can i set it up so that it asks me from what date to what date to make the report from.
thanks,

[D Willett]

V

You can do this one of two ways.
1. A static parameter report which uses the first day and the last day of the week.
This way the report would have to be run within the week of the report you require.

2. Presuming you are using a query to base the report on, you can add parameter requests to the
query criteria. This will ask you which date to start from, and which date to end on and pulling data between these dates.
This method also allows you to select a wider range of dates, ie a monthly or yearly report.
In your query design view, under the date field you want to run the date range.
In the "criteria" field, put the following:

>=[StartDate] And <=[EndDate]

When you run the report, you will be prompted for StartDate and EndDate.

To make things fancier, a form can be created which will popup when the report is run.
For now, try the example I have given.

[HansV]

The simplest way is to use a parameter query.

If the Record Source of your report is a table, create a query based on that table. If it is a query, fine.
Open the query that acts as Record Source in design view.
In the Criteria line for the field that contains the relevant date, enter the following expression:

Between [Start Date] And [Start Date]+6

When you switch to datasheet view, you'll get an input window in which you can enter the start date. You'll also get the parameter prompt when you open the report.

A more elegant solution is to use a form with a text box in which the user can enter the date; you can even link this to a Calendar control. Say that your form is named frmDate and the text box is named txtStartDate. The expression in the criteria line of the query would become

Between [Forms]![frmDate]![txtStartDate] And [Forms]![frmDate]![txtStartDate]+6

Note: this will make the query (and hence the report) return all records for which the date is in the period from the start date up to and including the date 6 days later, so the period comprises one week of 7 days. You can very this according to your needs.

[subyqa]

Is there an easy way to apply this same concept to a monthly report?
Feb - 28 / 30 or 31 days for others. How do I overcome this variance?

[HansV]

You can use the DateAdd function for this. Replace

[Forms]![frmDate]![txtStartDate]+6

by

DateAdd("m",1,[Forms]![frmDate]![txtStartDate])-1

(for instance, if txtStartDate is 11/20/2003, DateAdd("m",1,[Forms]![frmDate]![txtStartDate]) will be 12/20/2003, so subtracting 1 results in 12/19/2003. The where-condition will select all dates from 11/20/2003 up to and including 12/19/2003.

Note: If txtStartDate is 01/31/2003, DateAdd will result in 02/29/2004, and subtracting 1 will result in 02/28/2003. And if txtDateStart is 02/29/2004, DateAdd will result in 03/29/2004, and subtracting 1 will result in 03/28/2004. This may not be what you want, but that's the way DateAdd works.

[donebb]

Hans,

How do I use the Calendar Control? What I would like to do is place it on a form and allow the user to select the date and then have a report open up. Can you point me in the right direction to learn more? I checked A2000 help and it wasn't much. Thanks.

[WTH]

Hi

Attached is a sample 97 Access DB. It uses a calendar (the code I think was posted in this forum) which I have modified to allow a user to select a week period for a report. It also displays whether there is any data for that week.

It may give you some ideas.

Regards
WTH