Windows 7 - Dinamically create a list of dates from a fix date to Date()

Asked By Silvi on 24-Jan-09 11:34 AM
Background: My users are required to inspect 15 sites on a daily basis. For
each site inspected they need to make a short report (record). This record
will have the date of the inspection and the site ID saved.

Goal: I need to write “something” in order to quickly spot which site was
NOT inspected and in what date.

I suspect that I should use the unmatched query wizard, however how can I
create a list of dates starting from a fix date (e.g. December 12, 2008 to
now) to compare against? I know I could create a table with each single date
in it but I am sure there is a better and smarter method. Any idea how?

I envision this code/query to dynamically create dates from a fix date to
Date()

Thanks you folks,
Silvio




KenSherida replied on 24-Jan-09 12:41 PM
Silvio:

You've partly answered your own question; an auxiliary Calendar table is the
most efficient solution.  Otherwise you'd need to write a VBA function which
steps through the dates form a base date.  You can easily create a Calendar
table of all dates by serially filling down a column in Excel and importing
that into Access as a table.  Or you can use the following 'toolkit' function
to create table covering whatever range you  are interested in and include
whatever days of the week are relevant if you don't want all seven included:

Public Function MakeCalendar_DAO(strtable As String, _
dtmStart As Date, _
dtmEnd As Date, _
ParamArray varDays() As Variant)

' Accepts:  Name of calendar table to be created: String.
'           Start date for calendar: DateTime.
'           End date for calendar: DateTime.
'           Days of week to be included in calendar
'           as value list, e,g 2,3,4,5,6 for Mon-Fri
'           (use 0 to include all days of week)

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strSQL As String
Dim dtmDate As Date
Dim varDay As Variant
Dim lngDayNum As Long

Set dbs = CurrentDb

' does table exist? If so get user confirmation to delete it
On Error Resume Next
Set tdf = dbs.TableDefs(strtable)
If Err = 0 Then
If MsgBox("Replace existing table: " & _
strtable & "?", vbYesNo + vbQuestion, _
strSQL = "DROP TABLE " & strtable
dbs.Execute strSQL
Else
Exit Function
End If
End If
On Error GoTo 0

' create new table
strSQL = "CREATE TABLE " & strtable & _
dbs.Execute strSQL

' refresh database window
Application.RefreshDatabaseWindow

If varDays(0) = 0 Then
' fill table with all dates
For dtmDate = dtmStart To dtmEnd
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _

dbs.Execute strSQL
Next dtmDate
Else
' fill table with dates of selected days of week only
For dtmDate = dtmStart To dtmEnd
For Each varDay In varDays()
If Weekday(dtmDate) = varDay Then
lngDayNum = lngDayNum + 1
strSQL = "INSERT INTO " & strtable & "(calDate) " & _
dbs.Execute strSQL
End If
Next varDay
Next dtmDate
End If

End Function

Say you wanted to create a calendar for Monday-Friday covering the range
2008 to 2015 you'd call it like so:

MakeCalendar_DAO "WeekdayCalendar", #2008-01-01#, #2015-12-31#, 2, 3, 4, 5, 6

To include all days:

MakeCalendar_DAO "Calendar", #2008-01-01#, #2015-12-31#, 0

That should suffice for a while, but you can easily extend it later using
the same function if necessary.

You can then create query to return all sites and dates when not inspected
within a date range, e.g.

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT DISTINCT siteID, calDate
FROM Inspections AS I1, Calendar
WHERE calDate BETWEEN
[Enter start date:] AND NZ([Enter end date:],DATE())
AND NOT EXISTS
(SELECT *
FROM Inspections AS I2
WHERE I2.siteID = I1.SiteID
And I2.inspectionDate = Calendar.calDate)
ORDER BY SiteID, calDate;

If no value is entered at the [Enter end date] parameter prompt then all
dates from the start date to the current date will be taken into account.

Ken Sheridan
Stafford, England
Tom van Stiphout replied on 24-Jan-09 01:14 PM
The "table with all dates in it" is a good solution. You fill it once
with all dates for the next 10 years and forget about it.

-Tom.
Microsoft Access MVP
Silvi replied on 24-Jan-09 02:10 PM
Thank you, I guess I will continue to create a table using dates created in
excel as I have done in the past. Looking at the query however; I could use
your expertise a little more. The query looks at the inspections table to
determine which inspection is missing when compared to the Calendar table.
However, there is one more table that needs to be included in the equation:
tblSites. The tblSites has list of ALL available sites.
The inspection table however, will have SiteID only if a site has been
inspected at some point which may not have any data for sites listed in
tblSites. Therefore, when comparing SiteID from inspections to the calendar
the query will list only sites listed in Inspection and not ALL the sites
listed in tblSites which have no inspection at all. Can your query be
modified to include this other piece? How?

Example:

tblSites
SiteID
1
2
3
…

Inspection table
SiteID  InspectionDate
1	1/1/09
2	1/1/09
…

Calendar table
Dates
1/1/09
1/2/09

Query Missing inspections:
Date       SiteID
1/1/09     3
1/2/09     1
1/2/09     2
1/2/09     3
John W. Vinson replied on 24-Jan-09 02:46 PM
... and let whoever's in charge of the database in 2019 debug it...!?

--

John W. Vinson [MVP]
KenSherida replied on 25-Jan-09 10:56 AM
Silvio:

Simply replace the inspections table with the sites table in the outer query:

PARAMETERS
[Enter start date:] DATETIME,
[Enter end date:] DATETIME;
SELECT DISTINCT calDate, siteID
FROM Sites, Calendar
WHERE calDate BETWEEN
[Enter start date:] AND NZ([Enter end date:],DATE())
AND NOT EXISTS
(SELECT *
FROM  Inspections
WHERE Inspections.siteID = Sites.siteID
And Inspections.inspectionDate = Calendar.calDate)
ORDER BY calDate, siteID;

Ken Sheridan
Stafford, England