5 minute time increments

  • Thread starter Thread starter Jaimeta
  • Start date Start date
J

Jaimeta

I have a module that is suppose to sort my PickTime into 5 minute increments
and put it in the TimeSegment Start column.

My table looks like this before I run the Module.

Picktime TimeSegmentStart
4/29/2010 11:59:55 PM
4/29/2010 11:59:57 PM
4/29/2010 11:59:59 PM
4/29/2010 11:59:59 PM
4/30/2010 12:00:00 AM
4/30/2010 12:00:00 AM
4/30/2010 12:00:00 AM
4/30/2010 12:00:00 AM
4/30/2010 12:00:03 AM
4/30/2010 12:00:03 AM
4/30/2010 12:00:03 AM
4/30/2010 12:00:04 AM
4/30/2010 12:00:04 AM


Then after Module it looks like this:

Picktime TimeSegmentStart
4/29/2010 11:59:55 PM 11:55:00 PM
4/29/2010 11:59:57 PM 11:55:00 PM
4/29/2010 11:59:59 PM 11:55:00 PM
4/29/2010 11:59:59 PM 11:55:00 PM
4/30/2010 12:00:00 AM
4/30/2010 12:00:00 AM
4/30/2010 12:00:00 AM
4/30/2010 12:00:00 AM
4/30/2010 12:00:03 AM 12:00:00 AM
4/30/2010 12:00:03 AM 12:00:00 AM
4/30/2010 12:00:03 AM 12:00:00 AM
4/30/2010 12:00:04 AM 12:00:00 AM
4/30/2010 12:00:04 AM 12:00:00 AM

The problem is that it keeps giving me 12:00:00 AM all the way down the
table. It needs to start over at 0 when the date changes.

Here is my Module

Option Compare Database


Public Function ParseTimes()
DBEngine.SetOption dbMaxLocksPerFile, 150000
'This code requires that the subject file (currently tbldi125p) has
a date/time field (sorted acesending) "PickTime"
'and a field "TimeSegmentStart" to accept results. It steps through
the file and writes into TimeSegmentStart
'the same time for all records that occur in a time increment
controlled by dteTimeIncrement

'Initialize variables
Dim rs As ADODB.Recordset
Dim strTbl As String
Dim dteTimeStart As Date
Dim dteTimeStop As Date
Dim dteTimeSegmentStart As Date
Dim dteTimeSegmentStop As Date
Dim dteTimeIncrement As Date
Dim dteDay As Date

'Time increments are the fractional part of Microsoft's date/time
'0:05:00 = 0.003472222222
'0:10:00 = 0.006944444444
'0:15:00 = 0.010416666667
'0:20:00 = 0.013888888889
'0:25:00 = 0.017361111111
'0:30:00 = 0.020833333333
'1:00:00 = 0.041666666667

'Assign value to variables
dteTimeIncrement = 0.003472222222
dteTimeSegmentStart = 0
dteTimeSegmentStop = dteTimeIncrement
strTbl = "tblDi125p"
Set rs = New ADODB.Recordset
'The next line reads data from the table
rs.Source = "SELECT " & picktime & ", " & timesegmentstart & " FROM " &
strTbl
rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenDynamic
rs.LockType = adLockOptimistic

'Open table, will be on the first row
rs.Open (strTbl)

'If the table is empty, then end
If rs.EOF Then
End
End If

'Start loop through the table
Do While Not rs.EOF

dteDay = Int(rs!picktime)
Do While (dteDay = Int(rs!picktime))


If (dteDay = Int(rs!picktime)) Then

Do While Not ((rs!picktime - Int(rs!picktime)) <
dteTimeSegmentStop)
'looking at the picktime and what increment it falls
into
dteTimeSegmentStop = dteTimeSegmentStop +
dteTimeIncrement
dteTimeSegmentStart = dteTimeSegmentStart +
dteTimeIncrement

Loop

Else

dteTimeSegmentStart = 0

End If

If ((rs!picktime - Int(rs!picktime)) < dteTimeSegmentStop) Then
rs!timesegmentstart = dteTimeSegmentStart


End If

'Updates this row in the table
rs.Update
rs.MoveNext

Loop
dteDay = Int(rs!picktime)
dteTimeSegmentStart = 0
Loop 'End loop - go to start

rs.Close
Set rs = Nothing

End Function
 
Why not use an update query that uses a simple function? This should be more
efficient than stepping through all the records in a table.

Our resident mathematical genius James Fortune recently posted a very clever
solution for rounding. His idea can be wrapped in a little function for
Access like so:

Public Function RoundTo(dblVal As Double _
, dblTo As Double _
, Optional intUpDown As Integer = -1) As Double

' rounds up by default.
' to round down pass 1 into function as
' optional intUpDown argument.
RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo

End Function

The following expression should return the time in 5 minute increments
TimeValue(CDate(RoundTo([SomeDateTime],#00:05:00#,1)))

If you have NULL values in the field you might need to include a filter to
eliminate those records or use a conditional statement in the SET clause to
test for nulls and handle it there.

UPDATE TableName
SET TimeSegmentStart = TimeValue(CDate(RoundTo(PickTime,#00:05:00#,1)))
WHERE PickTime Between StartDate and EndDate



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Jaimete -

It is returning midnight because your else statement is setting the
dteTimeSegmentStart to zero. Simplify to one loop like this (untested):

'Start loop through the table
Do While Not rs.EOF

'If a new day is encountered, then reset TimeStart to zero
If dteDay <> Int(rs!picktime) Then
dteTimeSegmentStart = 0
dteDay = Int(rs!picktime)
End If
'looking at the picktime and what increment it falls into
dteTimeSegmentStop = dteTimeSegmentStop + dteTimeIncrement
dteTimeSegmentStart = dteTimeSegmentStart + dteTimeIncrement

If ((rs!picktime - Int(rs!picktime)) < dteTimeSegmentStop) Then
rs!timesegmentstart = dteTimeSegmentStart
End If

'Updates this row in the table
rs.Update
rs.MoveNext

Loop 'End loop - go to start
 
Back
Top