Rounding Time
in [Getting Started]
|
From: KenSheridan via AccessMonster.com on 19 May 2010 13:51 The function is designed to cater for any numeric data type, not just date/time values, which is why its arguments are declared as Double. In your case it just so happens that you dealing with date/time values, so it make sense to declare the variable for the value to be passed to the function as Date. A date/time value is implemented as a 64 bit floating point number in fact; we just normally see it in a date/time format. In the original thread in which I posted this function there was subsequent input from David Fenton, who made pertinent points about the advisability of breaking the expression down and assigning the result of each constituent operation to its own variable to avoid rounding errors. This lead to the amendment of the function to: Public Function RoundToInterval(dblVal As Double, _ dblTo As Double, _ Optional blnUp As Boolean = True) As Double ' rounds up by default. ' to round down pass False into function as ' optional UpDown argument Dim intUpDown As Integer Dim lngTestValue As Long Dim dblTestValue As Double Dim dblDenominator As Double If blnUp Then intUpDown = -1 Else intUpDown = 1 End If dblDenominator = intUpDown * dblTo dblTestValue = dblVal / dblDenominator lngTestValue = Int(dblTestValue) RoundToInterval = intUpDown * lngTestValue * dblTo End Function It would be called in the same way, e.g. to round up, which is the default: CDate(RoundToInterval(#8:14:59#,#00:15:00#)) or to round down: CDate(RoundToInterval(#8:14:59#,#00:15:00#,False)) Ken Sheridan Stafford, England Bre-x wrote: >Hi > >You are declaring the dTime as a Date Variable >the first variable that the RoundTo Function is expecting is a Double > >I am missing something here? > >> Literal example >> CDate(Roundto(#8:14:59#,#00:15:00#)) >[quoted text clipped - 3 lines] >> dTime = #8:14:59# >> CDate(Roundto(dTime,#00:15:00#)) > > Public Function RoundTo(dblVal As Double _ > , dblTo As Double _ > , Optional intUpDown As Integer = -1) As Double > > ' rounds up by default. > ' to round down pass 1 into function as > ' optional intUpDown argument. > RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1
From: Bre-x on 19 May 2010 14:29 Thank you Ken and Douglas I did change the dblVal to a Date, and wooaaaallllllaaaaa It works!!! Thank you once again Bre-x "KenSheridan via AccessMonster.com" <u51882(a)uwe> wrote in message news:a83f6250de6c3(a)uwe... > The function is designed to cater for any numeric data type, not just > date/time values, which is why its arguments are declared as Double. In > your > case it just so happens that you dealing with date/time values, so it make > sense to declare the variable for the value to be passed to the function > as > Date. A date/time value is implemented as a 64 bit floating point number > in > fact; we just normally see it in a date/time format. > > In the original thread in which I posted this function there was > subsequent > input from David Fenton, who made pertinent points about the advisability > of > breaking the expression down and assigning the result of each constituent > operation to its own variable to avoid rounding errors. This lead to the > amendment of the function to: > > Public Function RoundToInterval(dblVal As Double, _ > dblTo As Double, _ > Optional blnUp As Boolean = True) As Double > > ' rounds up by default. > ' to round down pass False into function as > ' optional UpDown argument > > Dim intUpDown As Integer > Dim lngTestValue As Long > Dim dblTestValue As Double > Dim dblDenominator As Double > > If blnUp Then > intUpDown = -1 > Else > intUpDown = 1 > End If > > dblDenominator = intUpDown * dblTo > dblTestValue = dblVal / dblDenominator > lngTestValue = Int(dblTestValue) > RoundToInterval = intUpDown * lngTestValue * dblTo > > End Function > > It would be called in the same way, e.g. to round up, which is the > default: > > CDate(RoundToInterval(#8:14:59#,#00:15:00#)) > > or to round down: > > CDate(RoundToInterval(#8:14:59#,#00:15:00#,False)) > > Ken Sheridan > Stafford, England > > Bre-x wrote: >>Hi >> >>You are declaring the dTime as a Date Variable >>the first variable that the RoundTo Function is expecting is a Double >> >>I am missing something here? >> >>> Literal example >>> CDate(Roundto(#8:14:59#,#00:15:00#)) >>[quoted text clipped - 3 lines] >>> dTime = #8:14:59# >>> CDate(Roundto(dTime,#00:15:00#)) >> >> Public Function RoundTo(dblVal As Double _ >> , dblTo As Double _ >> , Optional intUpDown As Integer = -1) As Double >> >> ' rounds up by default. >> ' to round down pass 1 into function as >> ' optional intUpDown argument. >> RoundTo = intUpDown * (Int(dblVal / (intUpDown * dblTo))) * dblTo > > -- > Message posted via AccessMonster.com > http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201005/1 >
From: John Spencer on 19 May 2010 15:46 Yes, DateValue returns ONLY the date portion of the string as a DateTime value. TimeValue returns 0NLY the Time portion of the string. If you want to convert the entire string and have both the date and time values use CDate to the do the conversion. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Bre-x wrote: > Hi, > > Public Sub temp() > Dim the_value As Date > the_value = DateValue("05/18/2010 8:16:01") > MsgBox RoundTime(the_value, 15, True) > End Sub > > The msgbox shows "05/18/2010" > It should show "05/18/2010 8:30" > > I must be missing something here!!! > > > > "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message > news:au36v55vsrj43s21hciuv3ksed2b3j3kac(a)4ax.com... >> On Tue, 18 May 2010 14:00:18 -0600, "Bre-x" <cholotron(a)hotmail.com> wrote: >> >>> Hi, Thank you for answering my post >>> >>> the RoundTime function is not working or I dont know how to use it >>> >>> Dim the_value As Date >>> the_value = "05/18/2010 8:15:01" >>> msgbox RoundTime(the_value, 15, True) >>> >>> this should show 8:30 >>> >>> Rigth? >> You're passing it a text string. It's expecting a Date/Time value. Try >> >> the_value = DateValue("05/18/2010 8:15:01") >> >> If it doesn't work (it did for me...) post back with more details. What >> happened? No result, wrong result, flames coming out of your monitor? >> >> -- >> >> John W. Vinson [MVP] > >
From: Bre-x on 19 May 2010 16:50
Thank you John It works very well!!!! "John Spencer" <spencer(a)chpdm.edu> wrote in message news:%23lmBCw49KHA.5716(a)TK2MSFTNGP06.phx.gbl... > Yes, DateValue returns ONLY the date portion of the string as a DateTime > value. TimeValue returns 0NLY the Time portion of the string. > > If you want to convert the entire string and have both the date and time > values use CDate to the do the conversion. > > John Spencer > Access MVP 2002-2005, 2007-2010 > The Hilltop Institute > University of Maryland Baltimore County > > Bre-x wrote: >> Hi, >> >> Public Sub temp() >> Dim the_value As Date >> the_value = DateValue("05/18/2010 8:16:01") >> MsgBox RoundTime(the_value, 15, True) >> End Sub >> >> The msgbox shows "05/18/2010" >> It should show "05/18/2010 8:30" >> >> I must be missing something here!!! >> >> >> >> "John W. Vinson" <jvinson(a)STOP_SPAM.WysardOfInfo.com> wrote in message >> news:au36v55vsrj43s21hciuv3ksed2b3j3kac(a)4ax.com... >>> On Tue, 18 May 2010 14:00:18 -0600, "Bre-x" <cholotron(a)hotmail.com> >>> wrote: >>> >>>> Hi, Thank you for answering my post >>>> >>>> the RoundTime function is not working or I dont know how to use it >>>> >>>> Dim the_value As Date >>>> the_value = "05/18/2010 8:15:01" >>>> msgbox RoundTime(the_value, 15, True) >>>> >>>> this should show 8:30 >>>> >>>> Rigth? >>> You're passing it a text string. It's expecting a Date/Time value. Try >>> >>> the_value = DateValue("05/18/2010 8:15:01") >>> >>> If it doesn't work (it did for me...) post back with more details. What >>> happened? No result, wrong result, flames coming out of your monitor? >>> >>> -- >>> >>> John W. Vinson [MVP] >> |