Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Need to make a Weekly Report (2000)

    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,

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need to make a Weekly Report (2000)

    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.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need to make a Weekly Report (2000)

    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.

  4. #4
    New Lounger
    Join Date
    Nov 2003
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need to make a Weekly Report (2000)

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need to make a Weekly Report (2000)

    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.

  6. #6
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    Anaheim, California, USA
    Posts
    560
    Thanks
    17
    Thanked 2 Times in 2 Posts

    Re: Need to make a Weekly Report (2000)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Sep 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    181
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Need to make a Weekly Report (2000)

    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
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •