Report based on dates missing in a table

[plucnik]

I hope I can explain this clearly.....just need a little help on this.

We have a input form that writes a date to a table for each day that a truck driver turns in his paperwork (actually a "drivers log" for those familiar with trucking). What I want to do is make a report based on the dates that the paperwork IS NOT turned in. How can I compare the data that I have in the table with a "calendar" and show what days are missing?
Thanks!
Peter

 

[barbarossaii]

Hi plucnik,

you could create another table w/ all (needed / possible) dates in it ("calendar").
Create a query and link the "calendar"-table and ur data table with a join (in querry design: Arrow from the date field in the calendar pointing to ur date field). Then set the criteria in the query to "is null" for ur date field.

HTH,
Barbarossa II

 

[plucnik]

thanks for the reply HTH....your idea would work but might cause me to make monthly tables for years to come to compare the data to. I wonder if there's another solution.....

 

[barbarossaii]

hi plucnik,

don't know another way.....But:

Try creating the "calender" once for - say - the next 15 yrs. Use the now() function in a criteria of a query to filter only the dates in the past from ur calendar. (this would make sure that ur query wont return 'driver x hasn't returned his papers for the whole yrs 2004-2005...')

If creating the 'calendar' with dates seems dull: just create a table containing integer numbers (which represent the dates); use lets say the format - function in a query to convert the numbers back into date. The advantage would be: u can create the numbers needed for all dates 2003 to 2015 in a minute via excel (fill & row, increment 1) and copy them in the access table.


HTH,
Barbarossa II

 

[plucnik]

HTH
I may have thought that creating the comparision tables was a bigger deal than it is. All I have to do is remember the rhyme "30 days has September, April, June......) and create each monthly table. The days of the week don't matter, only the dates. One table for each month (each would be good for eternity) will do. The only thing that I would have to compensate for is February in a leap year. Thanks for "making me think" Duh!
Peter

 

[barbarossaii]

plucnik,

as each date can be converted to a integer no and vice versa, I suppose u won't need to work around the "leap-february", if u create one table containing just integer numbers as a "calendar":
37001
37002
37003
37004
37005
......

and convert each number in a query into a date via a function; Result:
37001 => 19.04.2001
37002 => 20.04.2001
37003 => 21.04.2001
37004 => 22.04.2001
37005 => 23.04.2001
... => ....

The function to convert the int => date would be sth like
<ur_new_date_field>: Format([<ur_int_field>];"dd.mm.yyyy")

But: as I use a German Version of A97 I can guess how the exact syntax of the function should be in the Engl. Version.

HTH,
Barbarossa II

 

[plucnik]

Danke HTH..what a great suggestion! I tried it and my version of Access comes out as a USA date (37942=11/17/03). You've been a great help....all I can say is ......haben sie einen groben tag!

 

[Rich]

The following function will add dates to a table for you
Function FillTableDteDue()
Dim DB As DATABASE
Dim rst As Recordset
Dim StartFrom As Date
Dim Period As Integer
Dim I As Variant
Period = Forms!SomeForm![SomeField]
StartFrom = Forms!SomeForm![SomeDateField]
Set DB = CurrentDb
Set rst = DB.OpenRecordset("DateDue")
rst.MoveFirst
For I = 0 To Period
rst.AddNew
rst!DateDue = DateAdd("dd", I + 1, StartFrom)
rst.Update
rst.MoveNext
Next I
rst.Close
Set rst = Nothing
DB.Close
Set DB = Nothing
Exit Function

 

[plucnik]

Rich....thanks for the additonal infomation and the time you took to give me the code to make the table. Have a great day "East Of Eden"!
Peter