Date Groups

[Justin]

I'm creating a report for total sales. The user will enter the date range
and time period (days, weeks, months, years) for the grouping. This all
works fine. I would like to display a time period even when no data exists.


I have
01/08 10
02/08 11
03/08 7
05/08 15

I want
01/08 10
02/08 11
03/08 7
04/08 0
05/08 15

Any assistance will be apreciated

 

[Michel Walsh]

You can use VBA code in the report. Keep the last date you printed in a
local variable. When it is time to print a new record (ordered by ascending
value of date) print previousDate + 1 as long as the previous date +1 is not
equal to the actual date to be printed. You can print a detail section
without moving to the 'next' record by setting the property NextRecord to
false (while leaving the properties MoveLayout and PrintSection to true).
See Access 2000 Developers' Handbook, chapter 9, pp 684-next for more
details.


Alternatively, use a driver table, such as Iotas, one field, its primary
key, iota, with values from 0 to, say, 999. Then, print on the query:

SELECT DMin("dates", "tableNameHere") + iota
FROM iotas
WHERE iota <= DMax("dates", "tableNameHere") - DMin("dates",
"tableNameHere")


which should produce all the dates ( as long as there is less than 999 days
to be printed, if your table iotas stops at 999 ).



Hoping it may help,
Vanderghast, Access MVP