Are you storing your values in a Field with the Data Type of
Date/Time?
You could use some time functions:
let
mytime be the Field name
Example
mytime = 02:07:30
Hour(
mytime) = 2
Minute(
mytime) = 7
second(
mytime) = 30
Now since you are wanting to round on half hours we only need to change the Minute value.
Let's break it down. Do you wish for:
1-30 = 30
31-0 = 0
We could use an IIF
http://www.599cd.com/tips/access/iif-function/
=IIF(Condition, Value If True, Value If False)
=IIF(Minute(mytime) < 30, 30, 0)
So building up the time from the original.
TimeSerial ( hour, minute, second )
Expr#: TimeSerial(Hour([field1]),IIf(Minute([field1])<30,30,0),Second([field1]))
02:30:30
You aren't taking seconds into account.
This maybe a little OTT but thought it might be handy to see some functions.
Other(s)
Expr#: Format((Int([field1]*48+0.999999999999))/48, "hh:nn:ss")
http://www.accessforums.net/queries/round-up-time-half-hour-9844.html
1 day = 1 as a integer.
24hrs = 1 day
60 mins = 1 hour
60 secs = 1 minutes
--
60 x 24 = 1440 minutes
1440 x 60 = 86400 seconds
1440 / 30 = 48
30 mins is 1/48 of a day.
VBA Solution
http://allenbrowne.com/round.html
Rounding dates and times
Note that the Date/Time data type in Access is a special kind of floating point type, where the fractional part represents the time of day. Consequently, Date/Time fields that have a time component are subject to floating point errors as well.
The function below rounds a date/time value to the specified number of seconds. For example, to round to the nearest half hour (30 * 60 seconds), use:
=RoundTime([MyDateTimeField], 1800)
Public Function RoundTime(varTime As Variant, Optional ByVal lngSeconds As Long = 900&) As Variant
'Purpose: Round a date/time value to the nearest number of seconds
'Arguments: varTime = the date/time value
' lngSeconds = number of seconds to round to.
' e.g. 60 for nearest minute,
' 600 for nearest 10 minutes,
' 3600 for nearest hour,
' 86400 for nearest day.
'Return: Rounded date/time value, or Null if no date/time passed in.
'Note: lngSeconds must be between 1 and 86400.
' Default rounds is nearest 15 minutes.
Dim lngSecondsOffset As Long
RoundTime = Null 'Initialize to return Null.
If Not IsError(varTime) Then
If IsDate(varTime) Then
If (lngSeconds < 1&) Or (lngSeconds > 86400) Then
lngSeconds = 1&
End If
lngSecondsOffset = lngSeconds * CLng(DateDiff("s", #12:00:00 AM#, TimeValue(varTime)) / lngSeconds)
RoundTime = DateAdd("s", lngSecondsOffset, DateValue(varTime))
End If
End If
End Function
