Discussion Locked

dating empty events in Access

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a report that shows all observations per day within a time period. I want to show 0 observations for days in the period when no observation happened. I could do this if I had a table of all dates, left-joining it to the observation table withWHERE on date, but I do not want to have to maintain such a table or have the user maintain it. I do have the user maintaining a table of holidays, and I can exclude weekends from consideration, but I want to include in the report non-holiday weekdays when nothing happened.

4 replies

by sl-campbell

Hi John,

John, I do something like this already and the way that I do it is by creating a temp table - in code.
I am doing this in VB6 and haven't done this in Access, but the logic is the same. When the user clicks on a button to open a report,I call a Proceedure in a Module that basically creates a temp table by getting all the data from the table in question and filling an empty table with it, using an Append query. Then I create a recordset that I use below.

Then the Sub parses thedate range passed to it and loops through the years, months and days.
Of course the Year is the outer loop, the month the next inner loop and the days the last inner loop. When the month changes you need to calc. the days in the month using the DateAdd function - DateAdd("d",-1,(DateAdd("m",1,"01/" & iMonth & iYear)) - Adds one month to the current month and formats it to the 1st of that month and then subtracts one day - this will also handle leap years.

In the date loop, when the date changes to a new day, I do a FindFirst on my recordset with the criteria set for that date, and if it is not found, then I do an AddNew and add the day for that customer to the Temp Table.

You will need to re-order the data in the table by date if you do it this way, or , do not append the data at first to the temp table, but in the loop, when a record is found for a certain date, add it then, and if not, then add the dummy day.

If you have more that one customer, or whatever that you need to report on, then you will need a second temp table, filling it with the data above, and once the loop is done move the data to the final temp table, emptying the second temp table and starting over again with the next customer.

ANOTHER possibility would be to create a temp table using the loop above, filling it with the dates ONLY and then doing a join or something, then, once the report is done, remove the temp table.

by john_wills

I intend to try suggestion #1. I am very inclined to accept it, although I will wait a while before doing so in case something better comes along.

This discussion is closed to new replies.