A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Rounding Time



 
 
Thread Tools Display Modes
  #11  
Old May 19th, 2010, 06:51 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Rounding Time

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/For...arted/201005/1

  #12  
Old May 19th, 2010, 07:29 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default Rounding Time

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@uwe wrote in message
news:a83f6250de6c3@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/For...arted/201005/1



  #13  
Old May 19th, 2010, 08:46 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Rounding Time

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" wrote in message
...
On Tue, 18 May 2010 14:00:18 -0600, "Bre-x" 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]



  #14  
Old May 19th, 2010, 09:50 PM posted to microsoft.public.access.gettingstarted
Bre-x
external usenet poster
 
Posts: 103
Default Rounding Time

Thank you John
It works very well!!!!




"John Spencer" wrote in message
...
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" wrote in message
...
On Tue, 18 May 2010 14:00:18 -0600, "Bre-x"
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]



 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:29 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.