I have some code in my database that calculates time between two date/time fields and also calculates the weekends and holidays. I did not create this code, so I'm at a bit of a loss why it's not working correctly.
I have to tables, one is called Working Hours, which has two fields, wStart and wEnd. In those fields, I have 08:30 and 18:30. The other table has the Holidays, two fields, hName, which has the name of the holiday, and hDate, which is the date of the holiday.
Here is the code:
The code is working, except when we have hours that go from Friday to Monday, it adds an extra 10 hours. During the week it works fine, and from Thursday to Monday it seems to work fine.
I am still a bit of an amature when it comes to writing code, so I can not seem to find the glitch here. I would appreciate any help you can give me! I do not like when a problem is not consistent, and this one is baffling me.
Thanks so much!
Kelly
I have to tables, one is called Working Hours, which has two fields, wStart and wEnd. In those fields, I have 08:30 and 18:30. The other table has the Holidays, two fields, hName, which has the name of the holiday, and hDate, which is the date of the holiday.
Here is the code:
Code:
Public Function HCalc(CtlS, CtlE, CtlReqCh) As Double
ReqCh = CtlReqCh
If ReqCh = False Or IsNull(CtlS) Or IsNull(CtlE) Then
HCalc = 0
Exit Function
End If
StDate = CtlS
EnDate = CtlE
StDateD = Format(StDate, "Short Date")
EnDateD = Format(EnDate, "Short Date")
If StDateD = EnDateD Then
Result = DateDiff("n", StDate, EnDate, vbUseSystemDayOfWeek)
Else
Set qdefH = CurrentDb.CreateQueryDef("", "SELECT * FROM WorkingHours;")
Set rstH = qdefH.OpenRecordset
With rstH
MinDay = DateDiff("n", !wStart, !wEnd, vbUseSystemDayOfWeek)
Result = DateDiff("n", StDateT, !wEnd, vbUseSystemDayOfWeek)
Result = Result + DateDiff("n", !wStart, EnDateT, vbUseSystemDayOfWeek)
.Close
StDateT = Format(StDate, "Short Time")
EnDateT = Format(EnDate, "Short Time")
End With
Set qdefH = CurrentDb.CreateQueryDef("", "SELECT * FROM Holiday WHERE Weekday(hDate)<6 And Year(hDate) Between " & Year(StDateD) & " And " & Year(EnDateD) & ";")
Set rstH = qdefH.OpenRecordset
With rstH
StDateD = DateAdd("d", 1, StDateD)
Do Until StDateD = EnDateD
If Weekday(StDateD) < 6 Then
If .EOF = False Then
.MoveFirst
Do Until .EOF
If StDateD = !hDate Then
Result = Result - MinDay
Exit Do
End If
.MoveNext
Loop
End If
Result = Result + MinDay
End If
StDateD = DateAdd("d", 1, StDateD)
Loop
.Close
End With
Set qdefH = Nothing
Set rstH = Nothing
End If
HCalc = Round(Result / 60, 2)
End Function
I am still a bit of an amature when it comes to writing code, so I can not seem to find the glitch here. I would appreciate any help you can give me! I do not like when a problem is not consistent, and this one is baffling me.
Thanks so much!
Kelly
Comment