Reset Running Total when Value Reached

[AceBK]

Hello,

I am trying to create a scheduling module of sorts for a sewing facility. I have x number of orders that are listed and have the sewing time in hours in a field. What I am looking to do is add a field that does a running total that will reset when it hits 8 hours. Is this possible? I am using Access 2013 and I do have reasonable experience with Access. The hope is that I will be able to associate these resets with days of the week too. So if I run the report today, Thursday, it will record the first 8 hours and will associate the day as Thursday and the next 8 hours as Friday. Any help on this would be greatly appreciated.

 

[arnelgp]

is this on report?
if it is you can use the OnFormat event of Detail section to set the Date.
on LayOut View of your report drag an unbound textbox (say Text15 for our example).

Dim total_time As Double
Dim i As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
total_time = total_time + CInt(Hour([SEWING TIME]))
If total_time >= 8 Then
i = i + 1
total_time = 0
End If
[Text15] = Format(Date + i, "dddd")
End Sub


'// note that this will only work on Print View of your report.

 

[AceBK]

Thank you for the response, I was originally trying to get this to work in the query, but I am just as happy as getting it to work on the report. I made a few adjustments to the code that you suggested, only because it was not quite on the mark. I am posting it here to ensure that it makes sense to the experts. It seems to work. I added a row count field because for some reason the total_time variable started with a random number, so the code worked when total_time would get reset. Also, I added a variable for hours, should the shift work overtime. I also changed the code near the end of the If statement from total_time = 0 to total_time = [TotalSewingTime]. I did this because when it equaled 0, it would in a sense skip a row which would skew the time calculations. I also added some calculations to adjust for the weekends. Does this make sense?

Dim total_time As Double
Dim i As Integer
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

'ovalue is an invisible counter of the rows.  The first row started with a random number
If ovalue = 1 Then
    i = 0
    total_time = 0
End If

total_time = [TotalSewingTime] + total_time
'Hours can be varied, so I added the variable to be filled in when the report opens
If total_time >= [hours] Then
    If Format(Date + i, "ddd") = "Fri" Then
        i = i + 3
    ElseIf Format(Date + i, "ddd") = "Sat" Then
        i = i + 2
    Else
        i = i + 1
    End If
'By making total_time = zero, it would miss a row of calculating by making it zero
total_time = [TotalSewingTime]
End If
weekday = Format(Date + i, "ddd mm/dd")

End Sub

 

[arnelgp]

glad you made it worked.