Round to nearest hour in a query (Access 2003)
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.
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?
Last edited by Hack; 04-03-2012 at 07:56 AM.
I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
Please use [Code]your code goes in here[/Code] tags when posting code.
Before posting your question, did you look here
Got a question on Linux? Visit our Linux sister site.
Modifications Required For VB6 Apps To Work On Vista
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
You can use the Hour() function.
SELECT StartDate, Hour(StartDate) As StartHour FROM MyTable ORDER BY StartDate DESC, Hour([StartDate]);
kk, Thanks! you guys are a big help as always!!
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.
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
It's not what you don't know that gets you in trouble. It's what you know that just ain't so. -Mark Twain
To Round Just add 30 minutes to the StartDate before using Hour()
SELECT StartDate, Hour(DateAdd("n", 30, StartDate)) as AdjustedHour FROM MyTable
Return a time value
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:
Hour([LoginTime]) + Minute([LoginTime]) \ 30,
Notice the use of \ which rounds down to the integer as Int does.
Originally Posted by Gustav
You sir, are awesome..
Here is what I did to get to the minute.
This rounds up to the nearest minute, to round down, simply remove the + 60
Int([Date]) + TimeSerial(Hour([Date]), Minute([Date]),
Second([Date]) \60 +60) As RoundedDateTime
Last edited by Hack; 04-03-2012 at 07:59 AM.
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.
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!
Works perfectly. Thank you sir.
Thanks for that, I was looking to do the same thing.
Last edited by daniel5; 07-10-2012 at 06:24 AM.
By Lennie in forum VB Classic
Last Post: 05-29-2008, 05:39 PM
By srivastavait in forum ASP.NET
Last Post: 05-08-2008, 09:09 AM
By Michelle in forum VB Classic
Last Post: 04-19-2002, 05:01 PM
By David Jones in forum Database
Last Post: 08-31-2001, 12:22 PM
By Marcos in forum ASP.NET
Last Post: 01-23-2001, 12:35 PM
Top DevX Stories
Easy Web Services with SQL Server 2005 HTTP Endpoints
JavaOne 2005: Java Platform Roadmap Focuses on Ease of Development, Sun Focuses on the "Free" in F.O.S.S.
Wed Yourself to UML with the Power of Associations
Microsoft to Add AJAX Capabilities to ASP.NET
IBM's Cloudscape Versus MySQL