Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Report Dates with no records (2003)

    I've been asked to create a report that shows the number of records entered each day within a user-entered date range. I capture the date at time of entry and can successfully report the number of records for that date using a cross-tab query. The twist is that my client wants dates with no records to be reflected on the report as well. On the table where records are captured, if nothing is not entered on a given date, there will be no records. How can I create a line on my report for something that doesn't exist?

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

    Re: Report Dates with no records (2003)

    You'd have to create a table that lists all dates,then create a query based on that table and what you already have, with a left join on the date field.

  3. #3
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Report Dates with no records (2003)

    That's what I was afraid of ...thanks :-).

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Report Dates with no records (2003)

    The (relatively) easy way to do this is to use Excel. Just put in a column heading, then the earliest possible date in the next row, the drag down with the fill handle.
    The dates will increment automatically. To make lots of dates quickly use Edit...Fill...Series.
    You can them import the sheet into Access as your new table.
    Regards
    John



  5. #5
    Lounger
    Join Date
    Sep 2006
    Location
    St. Augustine, Florida, USA
    Posts
    28
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: Report Dates with no records (2003)

    Yup! Did that already. Thanks for the reinforcement, though!

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Warwick, Warwickshire, England
    Posts
    189
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Dates with no records (2003)

    You could create a blank table each time to which you append records in a loop starting from the start date of the report and ending with the end date.
    This way you wouldn't have to make any changes to the table if they wanted to report before/after the first/last date you have manually entered.

Posting Permissions

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