Incorrect sorting in report.

[jonesy29847]

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.

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.

[pbaldy]

Sorting for a report has to be specified in Sorting and Grouping. In 2007 it's on the Design tab, Grouping and Totals section.

[jonesy29847]

Perfect, Thanks.