Hi,
I've got some code that takes an initial date, then calculates a number of different dates based on a various number of working days after that date.
The problem is that, it's calculating the first 2 dates correctly, then subsequent dates are all 1 day extra than they should be??
My code is as follows - the Date_Notes_Due and Date_Chronology_Due_To_CR fields are fine, it's the dates after. The function beneath checks a table of Public Holidays and makes adjustments accordingly, but there are no public holidays that fall in between
i.e. a record with initial date of 8th Jan 16 - Date Report Due To CR field should be 18th Feb 16 but it's showing 19th Feb 16, and so on.
Can anyone help? It's just odd that it's doing the first 2 fields correctly, then not??!
I've got some code that takes an initial date, then calculates a number of different dates based on a various number of working days after that date.
The problem is that, it's calculating the first 2 dates correctly, then subsequent dates are all 1 day extra than they should be??
My code is as follows - the Date_Notes_Due and Date_Chronology_Due_To_CR fields are fine, it's the dates after. The function beneath checks a table of Public Holidays and makes adjustments accordingly, but there are no public holidays that fall in between
i.e. a record with initial date of 8th Jan 16 - Date Report Due To CR field should be 18th Feb 16 but it's showing 19th Feb 16, and so on.
Can anyone help? It's just odd that it's doing the first 2 fields correctly, then not??!
Code:
Case "Natural Causes"
strSQL = "UPDATE tbl_Death SET Date_Notes_Due = #" & Format(addduedate(Me.txtDateDeath, "2"), "mm/dd/yyyy") & "#," & _
"Date_Chronology_Due_To_CR = #" & Format(addduedate(Me.txtDateDeath, "10"), "mm/dd/yyyy") & "#," & _
"Date_Report_Due_From_CR = #" & Format(addduedate(Me.txtDateDeath, "29"), "mm/dd/yyyy") & "#," & _
"Date_Report_Due_To_QA_Panel = #" & Format(addduedate(Me.txtDateDeath, "30"), "mm/dd/yyyy") & "#," & _
"Date_Report_Due_From_QA_Panel = #" & Format(addduedate(Me.txtDateDeath, "40"), "mm/dd/yyyy") & "#," & _
"Date_Report_Due_To_Commissioner = #" & Format(addduedate(Me.txtDateDeath, "45"), "mm/dd/yyyy") & "#," & _
"Date_Report_Due_To_PPO = #" & Format(addduedate(Me.txtDateDeath, "50"), "mm/dd/yyyy") & "# WHERE tbl_Death.Death_ID = " & Me.txtDeathID & ""
--------------------------------------------------
Public Function addduedate(startdate As Date, numday As Integer) As Date
Dim rst As Recordset
Dim db As Database
Dim duedate As Date
Dim icount As Integer
On Error GoTo errhandlers:
Set db = CurrentDb
Set rst = db.OpenRecordset("tbl_holidays", dbOpenSnapshot)
icount = 0
duedate = startdate
Do While icount < numday
duedate = duedate + 1
If Weekday(duedate) > 1 And Weekday(duedate) < 7 Then
rst.FindFirst "[holidaydate] = #" & duedate & "#"
If rst.NoMatch Then
icount = icount + 1
End If
End If
Loop
addduedate = duedate
exit_errhandlers:
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Function
errhandlers:
MsgBox Err.Description, vbExclamation
Resume Next
End Function