David,
Cool problem, I had fun working on it.
Here is what I came up with. A quick caviat before I put in the code. This
solution does not scale really well. It works for a single user database and
a set of dates that is small. If you are having multiple users access the
database at the same time or are working with thousands of dates at a time,
this solution might not work (the multiple user scenario), or might have
performance issues (thousands of dates scenario).
Now, on to the solution. I created a simulation of your table but I only
used the date field itself. I have comments in the code that explain it.
--------------------------
Dim date1 As String
Dim date2 As String
Dim X As Integer
'
' See if we already have a temp date table. If so,
' delete it now.
'
Dim tmpExists As Boolean
tmpExists = False
' step through the tables looking for the temp one
For X = 0 To Application.CurrentDb.TableDefs.Count - 1
If Application.CurrentDb.TableDefs(X).Name = "tmpDates" Then
' we found an existing temp table. it needs to be deleted
tmpExists = True
Exit For
End If
Next
' delete the temp table if it exists
If tmpExists Then
Application.CurrentDb.Execute ("DROP TABLE tmpDates")
End If
' create a fresh temp table to hold the generated
' weekdates
Application.CurrentDb.Execute ("CREATE TABLE tmpDates (potDate date);")
' get the start and end dates
date1 = InputBox("Enter date 1")
date2 = InputBox("Enter date 2")
Dim d1 As Date
Dim d2 As Date
Dim curDate As Date
Dim dateStr As String
Dim dateDay As String
' check the validity of the dates entered
If Not IsDate(date1) Or Not IsDate(date2) Then
MsgBox "Invalid Date Entered"
Exit Sub
End If
' convert to actual dates
d1 = CDate(date1)
d2 = CDate(date2)
' make sure it's a valid date range
If d2 <= d1 Then
MsgBox "Invalid Date Range"
Exit Sub
End If
' step from start date to end date, one day at a time.
For X = 0 To CInt(d2 - d1)
curDate = d1 + X
' get what day of the week this date is
dateDay = Weekday(curDate)
' if it is not a weekend, add it to our temp date table
If dateDay <> 1 And dateDay <> 7 Then
Application.CurrentDb.Execute ("Insert into tmpDates Values(#" &
curDate & "#);")
End If
Next
' now the temp date table holds all possible
' non-weekend dates for the date range.
' now we need to see what dates are in that range
' that are not in the table we are trying to find the
' missing dates for
..
Dim rs As Recordset
'
' get all the dates from the temp date table that are
' not represented in our "MyDates" table (that was my
' test table with the date field, replace this with your
' table you're actually working with, and change
' "MyDate" to your primary key date field
'
Set rs = Application.CurrentDb.OpenRecordset("Select potDate from
tmpDates where potDate Not In (Select MyDate from MyDates)")
' step through the resulting record set and
' pull out the dates that were missing
Call rs.MoveFirst
While Not rs.EOF
MsgBox rs(0)
Call rs.MoveNext
Wend
