L
lsgKelly
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:
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
For the most part, this code seems to be working. But when I use the Hcalc
in a query, I get results, but sometimes when I click on the field with the
calculation, the number changes. It doesn't do it for every record, just
some. Here is an example of one that changes:
The calculation:
cTotalHours: HCalc([oDateSentTc],[oFinalDateT],Yes)
Result before I click on the field that is calculated:
10/2/2009 3:22:04 PM 10/7/2009 1:37:00 PM 30.03
10/2/2009 3:22:13 PM 10/7/2009 1:37:00 PM 28.25
After I clck on the field:
10/2/2009 3:22:04 PM 10/7/2009 1:37:00 PM 28.25
10/2/2009 3:22:13 PM 10/7/2009 1:37:00 PM 28.25
Does anyone have any idea what would cause this to happen?
Thanks in advance.
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:
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
For the most part, this code seems to be working. But when I use the Hcalc
in a query, I get results, but sometimes when I click on the field with the
calculation, the number changes. It doesn't do it for every record, just
some. Here is an example of one that changes:
The calculation:
cTotalHours: HCalc([oDateSentTc],[oFinalDateT],Yes)
Result before I click on the field that is calculated:
10/2/2009 3:22:04 PM 10/7/2009 1:37:00 PM 30.03
10/2/2009 3:22:13 PM 10/7/2009 1:37:00 PM 28.25
After I clck on the field:
10/2/2009 3:22:04 PM 10/7/2009 1:37:00 PM 28.25
10/2/2009 3:22:13 PM 10/7/2009 1:37:00 PM 28.25
Does anyone have any idea what would cause this to happen?
Thanks in advance.