Floor function

[Jessica]

Greetings!

How can I use the Floor function (found in Excel) in a module for Access?
What I have it doing in Excel is taking a date time (e.g. 3/2/2005 22:25:23)
and by using the floor function get it to the nearest hour (Floor(3/2/2005
22:25:23, 1/24) so that I get 3/2/2005 22:00:00. I then have to add values
to this date/time. How can I get access to do the same thing? I tried using
the round function (round(3/2/2005 22:25:23, 1/24) but got 38143 as a value
rather then a date value. Any ideas on how to correct this?

Thanks!

Jessica

 

[Brendan Reynolds]

The following assumes that you want to round up if the minute portion of the
time is 30 or greater, or down if the minute portion is less than 30.
Hopefully you should be able to see how to modify this if that's not exactly
what you want ...

Public Function ToNearestHour(ByVal TheDate As Date) As Date

Dim dtmWork As Date

dtmWork = DateSerial(Year(TheDate), Month(TheDate), Day(TheDate)) +
TimeSerial(Hour(TheDate), 0, 0)
If Minute(TheDate) >= 30 Then
dtmWork = DateAdd("h", 1, dtmWork)
End If
ToNearestHour = dtmWork

End Function

Some tests in the Immediate window ...

? tonearesthour(#21:29#)
21:00:00
? tonearesthour(#21:30#)
22:00:00
? tonearesthour(#22 March 2005 21:29#)
22/03/2005 21:00:00
? tonearesthour(#22 March 2005 21:30#)
22/03/2005 22:00:00