Round to nearest hour in a query (Access 2003)

[Aquanut]

Hey-

I am trying to create a query to sort items by date and blocks of time for the
purpose of monitoring the usage of a facility that we own. In this query we will count the number of users who enter the database (It uses a logon Mechanism to record data) for when they entered the facility. We are going to group it by each hour.

I've Googled this for a while with no avail. I do, however, have the date sorting down. Can someone please help? Or, if I am going about this wrong, just let me know. Any advice is appreciated.

-Aquanut

[Hack]

Show us how you are going about it.

With respect to your question, what code do you have so far?

Are you doing this in Access VBA?

[Aquanut]

I am just using an access query, I don't have anything besides the query sorting dates.

I don't have the code, but haven't really found a way to use VBA in queries.

but if it was possible to run a query in VBA, I don't know how to do it.
I tend to use MySQL with a C++ front end I wrote, my work wants me to
do this.

-Aquanut

[Ron Weller]

You can use the Hour() function.
SELECT StartDate, Hour(StartDate) As StartHour FROM MyTable ORDER BY StartDate DESC, Hour([StartDate]);

[Aquanut]

kk, Thanks! you guys are a big help as always!!

[Aquanut]

Actually, the hour function doesn't help. It justs returns the current hour. I need to be able to round it, due to the fact it is used to monitor usage. So ten minutes before each hour is on the next hour.

Like I said before, I think I might be going about this wrong, but I don't us access very much.

[Slope]

Assuming you want to round up for anything greater than 30 minutes past the hour. Try:

SELECT StartDate, IIF(MINUTE(StartDate) > 30, HOUR(DATEADD("h", 1, StartDate)), HOUR(StartDate)) as StartHour
FROM MyTable

[Ron Weller]

To Round Just add 30 minutes to the StartDate before using Hour()
SELECT StartDate, Hour(DateAdd("n", 30, StartDate)) as AdjustedHour FROM MyTable

[Gustav]

As I guess you wish a DateTime value to be returned, and a time within 30 minutes before Midnight to be rounded correctly, it would be easier to use TimeSerial:

SELECT
LoginTime,
Int([LoginTime]) +
TimeSerial(
Hour([LoginTime]) + Minute([LoginTime]) \ 30,
0,
0) AS
RoundedLoginTime
FROM
tblYourTable;

Notice the use of \ which rounds down to the integer as Int does.

/gustav

[rkguy]

As I guess you wish a DateTime value to be returned, and a time within 30 minutes before Midnight to be rounded correctly, it would be easier to use TimeSerial:

SELECT
LoginTime,
Int([LoginTime]) +
TimeSerial(
Hour([LoginTime]) + Minute([LoginTime]) \ 30,
0,
0) AS
RoundedLoginTime
FROM
tblYourTable;

Notice the use of \ which rounds down to the integer as Int does.

/gustav

You sir, are awesome..

Here is what I did to get to the minute.

SELECT [Date],
Int([Date]) + TimeSerial(Hour([Date]), Minute([Date]), 
Second([Date]) \60 +60) As RoundedDateTime

This rounds up to the nearest minute, to round down, simply remove the + 60

[vacpro]

Can anyone help me out please? I am using this expression to round time down (backwards) to the nearest 15 minutes - eg: 6:53 becomes 6:45. Works great.

Expr1: TimeSerial(Hour([ENDTIME]),(Minute([ENDTIME])\15)*15,0)

What I can't figure out is how to go the other way to round the time up (forwards) to the nearest 15 minutes - eg: 6:53 becomes 7:00.

Expr2: TimeSerial(Hour([STARTTIME]),(Minute([STARTTIME]) ??? ,0)

Is there a simple answer for this? Thank-you!

[Ron Weller]

Try This:
?TimeSerial(Hour([ENDTIME]),((Minute([ENDTIME])+14)\15)*15,0)

[vacpro]

Works perfectly. Thank you sir.

[daniel5]

Thanks for that, I was looking to do the same thing.