Heenan, Lambert
Lambert.Heenan at AIG.com
Thu Jan 8 12:36:43 CST 2004
But your TimeHour function returns a Date data type, and it uses TimeSerial to generate the return value, so once again, if your input value is more the 24 * 60 minutes then you will get back a nonsense date and time value. I repeat, if you are interesting in time durations then you need to work with numerical values, not Date/Time values. The Access Date/Time data type is inherently tied to the concept of a date - a point on the time line, which has nothing to do with the idea of a duration. This is why DateDiff returns a Long value, not some kind of date value. "If you wish to count more than 24 hours, here's a quick solution for seconds. One line (no line break): = Format(622081 \ 3600, "00:") & Format((622081 Mod 3600) \ 60, "00:") & Format(622081 Mod 60, "00") " Exactly, this is just the type of code you would put in a custom function to format durations so they display as hr:min:sec. Why type all that code each time you want to format a duration (like 62,2081 seconds) when you can just call a function - FormatAsTime(622081) ???? Lambert > -----Original Message----- > From: Gustav Brock [SMTP:gustav at cactus.dk] > Sent: Thursday, January 08, 2004 1:16 PM > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] Formatting time - minutes to hour:minutes > > Hi Lambert > > > If there's more that 24 * 60 minutes then TimeSerial will spit out a > > date/time like this... > > > ? TimeSerial(26,1,1) > > 12/31/1899 2:01:01 AM > > Of course, that's why I included the TimeHour() function. > If you wish to count more than 24 hours, here's a quick solution for > seconds. One line (no line break): > > = Format(622081 \ 3600, "00:") & > Format((622081 Mod 3600) \ 60, "00:") & > Format(622081 Mod 60, "00") > > The backslash rounds down, thus this will return a string: > 172:48:01 > > > The problem that Cynthia is having is simply that she is interested in > Time > > DURATION, but all the Time formatting and TimeSerial, DateDiff stuff is > > concerned with POINTS in time (a date and a time of day). > > > "Ugly" or not, when dealing with durations (which have nothing to do > with > > dates) you always have to do the math yourself to get hour minutes and > > seconds displayed. All you need is a simple function that given a number > of > > minutes (or seconds) will do the math and formatting and return the > > appropriate STRING value for display of reports and forms. > > For the "time duration" that is right. However, if you create a pseudo > timevalue (a time from Access' time "zero"), you have the Format() > function and all other formatting capabilities of Access ready at hand > - thus you eliminate the need to create even a simple function. > > For "always do the math yourself" I see no reason to struggle with > that; DateDiff() works fine except for a minor bug in the week count > for some leap years. > > /gustav > > > >> If you wish to use the features of formatting a date/time-value you > >> need to convert the amount of minutes to a timevalue - like here where > >> the amount of time is in hours: > >> > >> <code> > >> > >> Function TimeHour(ByVal curHMS As Currency) As Date > >> > >> ' Rounds and converts curHMS (number of hours as decimalnumber). > >> ' Returns a time value, hh:nn:ss. > >> ' Gustav Brock, Cactus Data ApS. > >> ' 1999-08-12. > >> > >> Dim lngH As Long > >> Dim lngM As Long > >> Dim lngS As Long > >> Dim curR As Currency > >> > >> ' No error handling needed. > >> On Error Resume Next > >> > >> ' Round to two decimals, and skip number of days if curHMS > 24. > >> ' If day count is needed, omit modulus like this: > >> ' curHMS = (CLng(curHMS * 10000) / 100 * 36) ' Mod 86400 > >> curHMS = (CLng(curHMS * 10000) / 100 * 36) Mod 86400 > >> > >> ' Calculate hours, minutes, and seconds. > >> lngH = Int(curHMS / 3600) > >> curR = curHMS - (lngH * 3600) > >> lngM = Int(curR / 60) > >> curR = curR - (lngM * 60) > >> lngS = CInt(curR) > >> > >> ' Create time value. > >> TimeHour = TimeSerial(lngH, lngM, lngS) > >> > >> End Function > >> > >> </code> > >> > >> Or you could try to simply feed your amount of minutes directly: > >> > >> datTime = TimeSerial(0, lngMinutes, 0) > >> > >> That may work - haven't tested it though. > >> > >> /gustav > >> > >> > >> > I have an application that tracks the amount of time spent on > projects. > >> I used DateDiff on the entry form in order to get the number of minutes > >> between the start and end times. The number is held > >> > in a field called "Time." In a report, I sum "Time" in order to get > the > >> total number of minutes. I need to show the total minutes in hh:mm > >> format on the report. I tried to simply format the > >> > field in the report, but that didn't work. > >> > >> > I tried dividing the minutes by 60, which gave me the number of hours > >> and a remainder. I then multiplied the remainder by 60 and then > divided > >> by 100 to get the number of seconds. This is getting > >> > ugly.... Am I making this harder than it is?? > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com