Rounding date to next hour

[THEJOESTER]

Hi, this database that I am making is for a train station that has runs on the hour. What I want is the code to make the current time (Time()) to be rounded to the next hour. Example: the time is 9:04, I need it to be rounded to 10:00. I need this so I can use it in queries so I dont have to keep going into the queries and changing the time value. Please Help. Much Appreciated.

 

[PHV]

A starting point:
TimeValue(Int((Time+#00:59:59#)*24) & ":00:00")

Don't run this code between 23:00:01 and 23:59:59 !

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THEJOESTER]

Hey I copied the TimeValue(Int((Time+#00:59:59#)*24) & ":00:00") into my query under my "Train Time" field so i would look up all the times for the next hour and this is what i get when i try to run it. "Data Type Mismatch in criteria expression" Any ideas? Please Help. Much Appreciated.

 

[PHV]

What is the data type of your "Train Time" field ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THEJOESTER]

The data type is Date/Time with a medium time as a "medium format": (EX: 5:45). Much Appreciated

 

[THEJOESTER]

Sorry Typo* The data type is Date/Time with a medium time as the format: (EX: 5:45). Much Appreciated

 

[PHV]

OK, what is your actual SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THEJOESTER]

SELECT Sum([Seniors]+[Adults]+[Children]+[Family Special]+[Family Extra]+[Comps]+[Group Special]) AS Expr1
FROM Customers
GROUP BY Customers.[Train Date], Customers.[Train Time], Customers.Reservations
HAVING (((Customers.[Train Date])=Date()) AND ((Customers.[Train Time])=TimeValue(Int(("Time"+#12/30/1899 0:59:59#)*24) & ":00:00")) AND ((Customers.Reservations)=Yes));

 

[MichaelRed]

mayhap a bit weighty ...

Public Function basRndTime(dtTimeIn As Date, _
                           Optional rndIntvl As String = "n", _
                           Optional rndIncr As Long = 30, _
                           Optional rndUp As Boolean = True)

    'Michael Red    2/26/04
    'Interval (rndIntvl) corresponds to an arument for DateAdd
    'Increment (rndIncr) corresponds to the # units for DateAdd
    'Direction (rndUp) is a flag to indicate the function should _
     round the value "up" (increase to the interval, whiich is the _
     default) or to round the value "Down" - e.g. Subtract from the value)

    Dim MyDiff As Long
    Dim MyIncr As Long

    MyDiff = (Format(dtTimeIn, rndIntvl) Mod rndIncr)

    If (Sgn(rndUp) <> 0) Then
        basRndTime = DateAdd(rndIntvl, rndIncr - MyDiff, dtTimeIn)
     Else
        basRndTime = DateAdd(rndIntvl, -MyDiff, dtTimeIn)
    End If

End Function

MichaelRed

 

[PHV]

Replace this:
Int(("Time"+
with this:
Int((Time()+

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[THEJOESTER]

Thanks, Works Great PHV