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
by john_wills
by john_wills
This discussion is closed to new replies.
Trending Web Development posts
Sort by Trending