I am using Access, to manage service reports, invoices, and expense reports for my contracting business. Since we work overtime, I have written a VB program to calculate, straight time, overtime, and double time hours.
The VB program, tracks ST, OT and DT, based on hours worked, DT is calculated based on Sunday work, and requires Monday through Sunday processing. I think that multiple weekdays are presented to the routine, thus the error.
HTML Code:
Function CalcOT(TaskDate As Date, hoursThisDay As Double) As Single
If Iter = 0 Then
Exit Function
End If
Iter = Iter - 1
dayOfWeek = DatePart("w", TaskDate)
Open "c:\OTCalc.txt" For Append As #1
If dayOfWeek = vbSunday Then
DTSum = DTSum + hoursThisDay
DTAcc = DTAcc + DTSum
Call DebugPrint(TaskDate, hoursThisDay, "vbSunday")
CalcOT = STAcc
Exit Function
End If
If dayOfWeek = vbMonday Then
STAcc = STAcc + hoursThisDay
STSum = 0
OTSum = 0
OTFlag = False
Call DebugPrint(TaskDate, hoursThisDay, "vbMonday")
CalcOT = STAcc
End If
If OTFlag = False And dayOfWeek <> vbMonday Then
STSum = STSum + hoursThisDay
STAcc = STAcc + hoursThisDay
If STSum > 50 Then
OTSum = STSum - 50
STSum = STSum - OTSum
STAcc = STAcc - OTSum
OTAcc = OTAcc + OTSum
'STAcc = STAcc + hoursThisDay
OTFlag = True
End If
Call DebugPrint(TaskDate, hoursThisDay, "<> vbMonday, OTFlag false on entry")
CalcOT = STAcc
Exit Function
End If
If OTFlag = True Then
OTSum = OTSum + hoursThisDay
OTAcc = OTAcc + hoursThisDay
Call DebugPrint(TaskDate, hoursThisDay, "default")
CalcOT = STAcc
Exit Function
End If
End Function
I ran invoices, for a two week period, and the hour calculations were incorrect. The report, does not seem to sort correctly, and I suspect this is the root cause of the hour calculation error. The query, used to create the report, specifies the sorting is to be by the date the task was performed.
I have not defined a key field in my table, and suspect that somehow this is reason the sort does not work correctly in the report, running the query results in correctly sorted records.
Any suggestions?
Thanks in advance for you help.