The function is designed to cater for any numeric data type, not just
date/time values, which is why its arguments are declared as Double. In
your
case it just so happens that you dealing with date/time values, so it make
sense to declare the variable for the value to be passed to the function
as
Date. A date/time value is implemented as a 64 bit floating point number
in
fact; we just normally see it in a date/time format.
In the original thread in which I posted this function there was
subsequent
input from David Fenton, who made pertinent points about the advisability
of
breaking the expression down and assigning the result of each constituent
operation to its own variable to avoid rounding errors. This lead to the
amendment of the function to:
Public Function RoundToInterval(dblVal As Double, _
dblTo As Double, _
Optional blnUp As Boolean = True) As Double
' rounds up by default.
' to round down pass False into function as
' optional UpDown argument
Dim intUpDown As Integer
Dim lngTestValue As Long
Dim dblTestValue As Double
Dim dblDenominator As Double
If blnUp Then
intUpDown = -1
Else
intUpDown = 1
End If
dblDenominator = intUpDown * dblTo
dblTestValue = dblVal / dblDenominator
lngTestValue = Int(dblTestValue)
RoundToInterval = intUpDown * lngTestValue * dblTo
End Function
It would be called in the same way, e.g. to round up, which is the
default:
CDate(RoundToInterval(#8:14:59#,#00:15:00#))
or to round down:
CDate(RoundToInterval(#8:14:59#,#00:15:00#,False))
Ken Sheridan
Stafford, England
Bre-x said:
Hi
You are declaring the dTime as a Date Variable
the first variable that the RoundTo Function is expecting is a Double
I am missing something here?
Literal example
CDate(Roundto(#8:14:59#,#00:15:00#))
[quoted text clipped - 3 lines]
dTime = #8:14:59#
CDate(Roundto(dTime,#00:15:00#))
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