Create table for filling in missing dates.

[r0nniem]

I have a form where users enter a date range for a query (field techs utilisation). The data in the query is missing some days when they didn't do any jobs that day, but I need know if a tech didn't do any jobs so I can make the data 0.

After thinking about it, here's my method for filling in dates: Create a temporary table that lists all the dates in the date range from the form. Link this table to my query to fill in blank days and mark as 0.

Example:
[forms]![frmzone]![begtransdate] = 01/02/07
[forms]![frmzone]![endtransdate] = 05/02/07

Temporary table:
FillinDates
01/02/07
02/02/07
03/02/07
04/02/07
05/02/07

I think this method will work, but I'm not sure how to go about it. If there's a another method for this I'd be happy to hear about it. If anyone could help that would, great

 

[dhookom]

I like to keep a table of all dates in my mdb. You can then add this table to a query and join where all the records from tblDates appear.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[BNPMike]

It seems to me that your need to know those days when nothing happens plus those when something does, equals your databases needs to hold everyday. If so I can't see what the form has got to do with it. I would be inclined to load the database at the begining of each period/year with zero entries and just let people update those days that they did something.

Alternatively: your query gives missing days - why can't your users work out that missing = 0?

 

[r0nniem]

This is for a field tech utilisation report. I need to know when there are no jobs completed as well as when jobs were completed. I have a table with manhours listed for each day of the week in each zone. I need to total the daily man hours and can't link when no date is there.

Thanks dhookom once again How do you know how far to go? What code do you use to write this table?

 

[dhookom]

I usually create a table of dates from the earliest I might need to maybe 10 years into the future. The easiest method for creating the table is using Excel. You can create a column of dates and then copy and paste it into your table.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]