Thanks to this thread I managed to produce a schedule for my projects.
Unfortunately something goes wrong when I insert dates with a "0" in the day in my schedule table like 10-11-2017 and 3-12-2017 (dd-mm-yyyy).
When I fill in "0" days I get no week nr or the wrong week nr back.
So I assume I have to change the second row of the code, I have tried several options but no result.
Hopefully someone can point me in the right direction.
Code:
Do While dCurrDate <= dEndDate sSQL = "INSERT INTO tblReportSetup (Nr, OffTimeID, DateOff) "
sSQL = sSQL & "SELECT [tblProjecten]![Nr], iif(#" & dCurrDate & "# between [startdate] and [enddate], [offtimeid],null) as Expr2, '" & Right("0" & DatePart("ww", dCurrDate, 2, 2), 2) & DatePart("yyyy", dCurrDate, 2, 2) & "' AS DateOff "
sSQL = sSQL & "FROM tblProjecten "
sSQL = sSQL & "RIGHT JOIN tblSchedule ON tblProjecten.Nr = tblSchedule.Nr"
Debug.Print sSQL
db.Execute sSQL
dCurrDate = DateAdd("ww", 1, dCurrDate)
Loop