Summing up time in sequences.

[Jovix]

Here is a tricky one, I need a piece of code that will look at a column in a table that contains time. Then it will sum up the times in sequences, for example...

If the column has 1:30, 1:31, and 1:32, each time being a different record, it will come to 2 minutes.

But if the column has 1:30, 1:31, 1:32, 1:50, 1:51 it will start a new sum after 1:32. So in this case I would have a sum of 2 minutes between 1:30 and 1:32. And a sum of 1 minute between 1:50 and 1:51.

hope this makes sense,
Thanks for you help.
J

 

[RickSpr]

It sounds like some kind of process control log file, where you're finding continuous runs, right?

All you really need to do is read sequentially and identify nonconsecutive records. Easy.

Sub GetUpTime()
    Const Interval = 62   ' max seconds between log entries
    Const TableName = "LogTable" ' name of the table
    Const FieldName = "Time"     ' name of the time field
    Const SQL = "SELECT " & FieldName & " FROM " & TableName _
        & " ORDER BY " & FieldName
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim datStart As Date, datLast As Date, intDur As Integer

    Set db = CurrentDb()
    Set rst = db.OpenRecordset(SQL)
    If Not rst.EOF Then
        datStart = rst.Fields(FieldName)
        datLast = datStart
        rst.MoveNext
        Do While Not rst.EOF
            If rst.Fields(FieldName) > DateAdd("s", Interval, datLast) Then
                intDur = DateDiff("n", datStart, datLast)
                Debug.Print datStart; " for"; intDur; _
                    " minutes"
                datStart = rst.Fields(FieldName)
            End If
            datLast = rst.Fields(FieldName)
            rst.MoveNext
        Loop
        intDur = DateDiff("n", datStart, datLast)
        Debug.Print datStart; " for"; intDur; " minutes"
    End If
    Set rst = Nothing
    Set db = Nothing
End Sub

Setting the Interval constant to 62 seconds allows for a slight delay in logging. I wasn't sure if that would be necessary.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Jovix]

Thanks Rick,

This looks like it might work, I'll play around with it and let you know how it goes. But I warn you, I might have some questions.

And you are right, it's a log file I'm looking at.

J