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, _
"Delete Table?") = vbYes Then
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 & _
"(calDate DATETIME, " & _
"CONSTRAINT PrimaryKey PRIMARY KEY (calDate))"
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) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
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) " & _
"VALUES(#" & Format(dtmDate, "mm/dd/yyyy") & "#)"
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
Silvio said:
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
