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
Jaimeta wrote:
> 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
>
|
Similar Threads
