Include dates where no dates exist....

[robmason10]

sounds daft I know but I am reporting on a 'timesheet' table - the table is populated when someone enters time for a particular day...we need the report to show ALL dates in a range regardeless of if there is an entry in the main table - unfortunately we cannot add a simple date table to the database. Any ideas how I can do this?

Thanks in advance you lovely people!

 

[pelajhia]

You might try an array of dates.
Set a loop to run from 1 to 365,
Start with Dec 31,2002 as your first date (or whenever).

datevar thisdate:=(2002,12,31);
nubervar x:=1;
while x<365
do
(
numbervar array year2003;
redim preserve year2003[x];
thisdate:=DateAdd('d',1,thisdate) //adds one day to date
year2003[x]:= thisdate;
x+1;
)

this should build an array of all the dates in the year (give or take a little tweaking.)

Then you should be able to compare with the following

if not ({yourdatefield} in year2003)
etc., etc.

 

[KRobinson]

The following was posted by Tek-Tips User Chelseatech in answer to a question I posed earlier:

Then use a formula to display the missing time slots using Previous and Next Functions. We have examples of doing that for missing days in some old issues of Crystal Clear. Check out Novemeber 2000 and the few months before that.

Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm