Hi Ken
Thanks for this code, just what I'm looking for
. I have a query though
if that is ok... ?
Sometimes it works and not others?
I've only got as far as creating the calendar routine so far, I use this
in
code:
Dim dtStart As Date, dtEnd As Date, msg
dtStart = Format(Me![StartDate], "mm\/dd\/yyyy")
dtEnd = Format(Me![EndDate], "mm\/dd\/yyyy")
MakeCalendar_DAO "tblCalendar", dtStart, dtEnd, 2, 3, 4, 5, 6
dtStart and dtEnd come from my form. If I choose 31/12/09 start date and
end
of March as EndDate and press my button which runs the code above, it
works
and populates the data correctly in my table. If I choose 04/01/10 though
it
doesn't work, there are no errors but the table is empty?
I have tried various dates to see if I can find a logical pattern but so
far
it seems random. Any ideas why intermittently it just creates an empty
table?
Thanks....
Sue
Ken Sheridan said:
Firstly build a calendar table with the following function:
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
To build a calendar table named WorkdaysCalendar of all weekdays from
2008
to 2018 say, call the function like so:
MakeCalendar_DAO "WorkdaysCalendar",#2008-01-01#,#2018-12-31#,2,3,4,5,6
Then delete all holiday dates from the table with:
DELETE *
FROM WorkdaysCalendar
WHERE EXISTS
(SELECT *
FROM tblHolidays
WHERE tblHolidays.HolidayDate = WorkdaysCalendar.CalDate);
You can repeat this periodically as new holidays are added to the
holidays
table.
You can then return the unpunched weekday/non-holiday days per employee,
e.g. for employee number 42 this year, with a query like this:
SELECT DISTINCT EmployeeID, CalDate
FROM TimeClock AS TC1, WorkdaysCalendar
WHERE YEAR(CalDate) = 2008
AND EmployeeID = 42
AND NOT EXISTS
(SELECT *
FROM TimeClock As TC2
WHERE TC2.EmployeeID = TC1.EmployeeID
AND TC2.PunchDate = WorkdaysCalendar.CalDate)
ORDER BY CalDate;
Ken Sheridan
Stafford, England
