Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Dates to calculate values (97)

    I've got a rather complicated report that I need to generate, and I'm stumped about how to set up the dates.

    I am dealing with circulation numbers for a newspaper. In the particular case that I'm dealing with, schools do not get their 'bulk' orders on days that school is not in session. I have my database set up so that there is a number of papers each school gets on each day of the week (since that can be different each day), a date that they start receiving papers, and the date that they stop receiving papers. There's also a one-to-many table which has dates which the school does not want to receive papers between those two dates.

    What I want to do is generate a report for each month which gives circulation numbers for each day, taking into account which schools do or don't get papers, and which day of the week it is.

    The problem that I'm running into is how I should go about telling Access the dates that I want included for the report, and how to increment daily so that I can get a full range of dates.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Using Dates to calculate values (97)

    The usual way to do this is to set a date range in the query that drives the report. Most commonly, the criteria for your query would look something like:
    <font color=blue>Between DateAdd("M",-1,Date()) And Date()</font color=blue>
    This would give you any circulation data for dates from the current date back one month. The DateAdd function is the trick here - you should be able to find info on it in the Access 97 help files.
    Wendell

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    St. Paul, Minnesota, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Dates to calculate values (97)

    Back to this topic for me. I've had to change my database design, because we discovered that we could export data from my database into the circulation system (yay for automation that even the geeks who run the circ system didn't know about!)

    But now I have to figure out how to generate the reports properly, and I'm still very confused about how to go about it. (I never did figure out the other way, either but here goes for this one)

    I'm thinking that I may have to set up two queries to actually filter the data properly.

    First, I need to learn how to tell Access that I want data for each day in a particular range of dates (generally a month at a time).
    Then, I need to tell the system to look at the start date, and make sure that it is equal to or less than the date in question. Then it needs to check that the end date is greater than the date in question (this is because of the way circulation is built, I know it's not logical to have it be the day after* the actual last day papers are needed). That would make the date range appropriate for the given date we're working on.
    Then, I need to select the field for the day of the week that that date falls on, and sum the total of all of the numbers it selects (I know the sum part is easy).

    My biggest problem seems to be figuring out how to make Access increment through the dates, as I want to build a report for each month that can be generated at any time in the future.

Posting Permissions

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