Int rounding down (calculated) whole numbers

[SimonG via AccessMonster.com]

I have a function during which a line passes a calculation to an integer,
usually all is fine, but occasionally, where the result of the calculation is
a whole number this is also rounded down (eg 12 is rounded down to 11). Note
this does not happen for all whole numbers passed, the two examples I
currently have are 12 and 28.
I suspect the cause of the problem to be in the phasing of the code for the
calculation, perhaps someone could look-it over and make some suggestions?

Background:
A function is required to round times to the nearest x mins (this element
taken as stand-alone works with no errors yet found), however the function
also needs to allow for an off-set:
eg from 7 o'clock the times to the nearest 15 mins are 7:00; 7:15; 7:30; 7:
45; 8:00 etc.
however from 7:10 (time off set by 10 mins) times are: 7:10; 7:25; 7:40; 7:
55; 8:10 etc

My “simple†solution to this is to remove the off-set time; round the the
nearest x mins; then add back the off-set.

Code:

Public Function TimeRoundDown(dteHighTime As Date, _
Optional intNearest As Integer = 60, _
Optional intMinPast As Integer = 0)
'
' This returns the time Rounded Down to the nearest interval
' if no interval is given time is rounded to nearest hour*.
'
' * mins past is used to round to nearest mins past the hour
' eg time of 7:20, rounding nearest 15 for 10 past the hour
' rounded down time is 7:10
'
Dim intTimeBlks As Double
Dim dblStdMin As Double
Dim dblConv As Double

dblStdMin = intMinPast / (24 * 60)
dblConv = 24 * 60 / nz(intNearest, 60)

dteHighTime = dteHighTime - dblStdMin

' Next line causes error:
intTimeBlks = dteHighTime * dblConv
'
TimeRoundDown = (Int(intTimeBlks) / dblConv) + dblStdMin

End Function

Known Problem values:
3:15 to the nearest 15, off-by 15 mins problem line returns 11 not 12
7:15 to the nearest 15, off-by 15 mins problem line returns 27 not 28

Any suggestions gladly received,
Simon

Ps
Access 2007 on XP or Vista pc.

 

[vanderghast]

I suspect you get something like 11.9999999999 (which is displayed as 12)
but when you applied int to it, it is rounded down to 11.


Try using DECIMAL values rather than Double:



Dim TimeBlks As Variant
Dim StdMin As Variant
Dim Conv As Variant

StdMin = MinPast / CDec(24 * 60)
Conv = CDec(24 * 60) / nz(Nearest, 60)
...



Vanderghast, Access MVP

 

[SimonG via AccessMonster.com]

Many thanks Vanderghast,

Took a bit of trial and error to get the CDec in the right place, but I think
I've cracked it now

For reference the completed code is:

Public Function TimeRoundDown(dteHighTime As Date, _
Optional intNearest As Integer = 60, _
Optional intMinPast As Integer = 0)
'
' This returns the time Rounded Down to the nearest interval
' if no interval is given, time is rounded to nearest hour*.
'
' * mins past is used to round to nearest mins past the hour
' eg time of 7:20, rounding nearest 15 for 10 past the hour
' rounded down time is 7:10
'
Dim intDayHours As Double
Dim dblStdMin As Double
Dim dblConv As Double

dblStdMin = intMinPast / (24 * 60)
dblConv = 24 * 60 / nz(intNearest, 60)

dteHighTime = dteHighTime - dblStdMin

intDayHours = CDec(dteHighTime * dblConv)
TimeRoundDown = (Int(intDayHours) / dblConv) + dblStdMin

End Function

and its opposite, for rounding times up is:

Public Function TimeRoundUp(dteLowTime As Date, _
Optional intNearest As Integer = 60, _
Optional intMinPast As Integer = 0)
'
' This returns the time Rounded Up to the nearest interval
' if no interval is given, time is rounded to nearest hour*.
'
' * mins past is used to round to nearest mins past the hour
' eg time of 7:20, rounding nearest 15 for 10 past the hour
' rounded up time is 7:25
'
Dim intDayHours As Double
Dim dblStdMin As Double
Dim dblConv As Double

dblStdMin = intMinPast / (24 * 60)
dblConv = 24 * 60 / nz(intNearest, 60)

dteLowTime = dteLowTime - dblStdMin

intDayHours = -CDec(dteLowTime * dblConv)
TimeRoundUp = (-Int(intDayHours) / dblConv) + dblStdMin

End Function