Time Format

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi
I'm trying to set a database that will keep a record of cumulative times -
does anyone know how to get Access to recognise times of grater than 24:00
(I need to keep records of cumulative times up to approx 5000 hours)

Thanks
 
Hi
I'm trying to set a database that will keep a record of cumulative
times - does anyone know how to get Access to recognise times of
grater than 24:00 (I need to keep records of cumulative times up to
approx 5000 hours)
Click to expand...

The thing to consider is that the Date/Time data type is designed to
store a *moment*, not a duration. So you're really better off storing
durations in units of minutes (or hours, or seconds, depending on the
resolution you need), and use a Number field -- usually a Long
Integer -- to hold the number of units. Then you use a simple routine
to convert that value to an "hours:minutes" representation whenever you
need to display it.
 
I'm trying to set a database that will keep a record of cumulative times -
does anyone know how to get Access to recognise times of grater than 24:00
(I need to keep records of cumulative times up to approx 5000 hours)
Click to expand...

If you aren't including full date values (such as start/stop times using
date/time values), you will need to store your values in another data type, such
as Number. If you need to calculate down to minutes, store the value to
represent "minutes" - in other words, store the value to represent the smallest
portion of time that you need to return in your calculations. Then you could
write a function to break down your value into hours, minutes, seconds, etc..
 
Hi,

To calculate total time, you need to split the time in hours and minnutes,
sum them separately, then use the function below, who returns you the total
time, as a string, in format HHHH:MM (which you can use on reports)

Suppose you have a field called TimeSpent in table TimeTest

dim nHours as integer, nMinutes as integer
dim rs as DAO.Recordset, db as DAO.Database
set db = currentdb()
set rs = db.openrecordset("select TimeSpent from TimeTest where <<< your
condition >>>")
if rs.recordcount >0
do while not rs.eof
nHours = nHours + Hour(rs![TimeSpent])
nMinutes = nMinutes + Minute(rs![TimeSpent])
rs.movenext
loop
endeif

debug.print "Total time is:", GetTime(nHours, nMinutes)

Public Function GetTime(ByVal nHours As Integer, ByVal nMinutes As Integer)
As String

Dim sResult As String
On Error GoTo GetTime_Error

Dim nMoreHrs As Integer, sHours As String
nMoreHrs = nMinutes / 60
nMinutes = nMinutes Mod 60
nHours = nHours + nMoreHrs
If nHours < 10 Then
sHours = "0" & nHours
Else
sHours = "" & nHours
End If

sResult = sHours & ":" & Right$("0" & nMinutes, 2)
GetTime = sResult

GetTime_Exit:
On Error GoTo 0
Exit Function

GetTime_Error:

MsgBoxLog "Error " & Err.number & " - " & Err.Description & vbCrLf & "In
procedure GetTime"
Resume GetTime_Exit

End Function


HTH,
Bogdan
 
Back
Top