Time Problem

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

Ok you have help me alot with my calculating database.
here is a problem that has come up
I have a simple start time and then a finish time i just want to subtract
the two the get the number of hour,minutes and seconds in one text box hear
is what it is showing now
Start Time 18:19:04
Finish Time 02:06:04
Total Time 04:13:00
Of course this is not right it should be 07:41:00
Here is what i have tried in bot a control source in a form and the
afterupdate box also
=[Finish Time]-[Start Time] that didnt work
=DateDiff("n",[Finish Time],[Start Time]) that didnt work that showed
12:00:00am
I also tried several different combo's of that but they didnt work

if any other info is needed please ask
bob

thank you for the help
 
You should have the date in addition to the time, since without it, Access
assumes you mean date as 30th December 1899, in both cases.

If you can't add the date, you can add 1 to the subtraction if the result
would have been negative:


? Time1 - Time2 + iif( Time1 < Time2, 1 , 0 )



Vanderghast, Access MVP



ps.: negative date_time value behave quite strangely if you are not aware
of a few things, the most important one is that the time part is always read
in absolute value, regardless of the sign:

? Format( -0.99999, "yyyy.mm.dd \t hh:nn:ss"), Format( -1.00001,
"yyyy.mm.dd \t hh:nn:ss")
1899.12.30 t 23:59:59 1899.12.29 t 00:00:01


note that while there is only a difference of 0,00002 between the two
values, before being formatted, the resulting dates are almost 48 hours
apart.
 
Let's assume you have the following controls on your form:

StartDate - the start date
StartTime - the start time
EndDate - the end date
EndTime - the end time

If that's the case, then here's what you can do:

'********* Start Code *************
Public Function CalcInterval()

Dim StartDateTime As Date
Dim EndDateTime As Date
Dim lngInterval As Long
Dim lngHours As Long
Dim lngMinutes As Long
Dim lngSeconds As Long
Dim strTotalDuration As String

StartDateTime = Me.StartDate & " " & Me.StartTime
EndDateTime = Me.EndDate & " " & Me.EndTime

lngInterval = DateDiff("s", StartDateTime, EndDateTime) ' Interval in seconds
lngHours = lngInterval \ 3600 '
Calculate hours
lngMinutes = (lngInterval - (lngHours * 3600)) \ 60 ' Calculate
minutes
lngSeconds = lngInterval - (lngHours * 3600) - (lngMinutes * 60) ' Calculate
seconds

'Now, put it all together
strTotalDuration = Trim(str(lngHours)) & ":" & Trim(str(lngMinutes)) & ":" & _
IIf(Len(str(lngSeconds)) = 2, Trim("0" & Trim(str(lngSeconds))),
Trim(str(lngSeconds))) & "."

'Give the user the answer
MsgBox "The interval between " & StartDateTime & " and " & EndDateTime & "
is " & strTotalDuration

End Function
'********* End Code *************

Please note that the above does not have anything to handle Nulls in any of
the fields, nor any other error handling. I tested it on 4 or 5 different
starting/ending dates/times and it worked, but I can't say for sure it will
work 100% all of the time (4 or 5 tries does NOT constitute proper debugging!
<grin!>).

Anyway, I hope that helps...

Regards, Chris

P.S. Btw, you were slightly off. the interval between 18:19:04 on one day
and 02:06:04 the following day is 07:47:00 not 07:41:00.
 
Bob,

Access date/time values are stored internally as double-precision floating
point numbers. This double-precision number represents the number of days
elapsed since the base date of 12/30/1899.

Each number contains a date component and a time component. The integer
portion, to the left of the decimal point, represents the date. The
fractional portion, to the right of the decimal point, represents the time.

December 24, 2003 @ 9:00pm would be stored internally in Access as
37979.875, where 37979 represents the number of days elapsed since 12/30/1899
and the 875 represents fractional portion of a 24 hour day.

To convert Access' Date/Time format into the equivalent double-precision
number the function CDbl() is used. Once the Date/Time formats are converted
into a 'real' number, they may be added, subtracted, etc. The function
CDate() is then used to convert the double-precision number back into a
Date/Time format.

Reference
http://office.microsoft.com/en-us/access/HA011102181033.aspx

So, some sample code
Private Sub Command0_Click()
Dim dTimeIn As Date, DTimeOut As Date
Dim DblTimeIn As Double, DblTimeOut As Double
dTimeIn = Me.TimeIn: DTimeOut = Me.TimeOut
DblTimeIn = CDbl(dTimeIn)
DblTimeOut = CDbl(DTimeOut)
MsgBox HoursAndMinutes(DblTimeOut - DblTimeIn)
End Sub

Public Function HoursAndMinutes(interval As Variant) As String
'*************************************************************
' Function HoursAndMinutes(interval As Variant) As String
' Returns time interval formatted as a hours:minutes string
'*************************************************************
Dim totalminutes As Long, totalseconds As Long
Dim hours As Long, minutes As Long, seconds As Long

If IsNull(interval) = True Then Exit Function

hours = Int(CSng(interval * 24))
If hours < 0 Then
hours = 24 + hours
End If

' 1440 = 24 hrs * 60 mins
totalminutes = Int(CSng(interval * 1440))
minutes = totalminutes Mod 60
If minutes < 0 Then
minutes = 60 + minutes
End If

' 86400 = 1440 * 60 secs
totalseconds = Int(CSng(interval * 86400))
seconds = totalseconds Mod 60
If seconds < 0 Then
seconds = 60 + seconds
End If

' Round up the minutes and adjust hours
If seconds > 30 Then minutes = minutes + 1
If minutes > 59 Then hours = hours + 1: minutes = 0
HoursAndMinutes = hours & ":" & Format(minutes, "00")
End Function

Hope this helps!
James

bob said:
Ok you have help me alot with my calculating database.
here is a problem that has come up
I have a simple start time and then a finish time i just want to subtract
the two the get the number of hour,minutes and seconds in one text box hear
is what it is showing now
Start Time 18:19:04
Finish Time 02:06:04
Total Time 04:13:00
Of course this is not right it should be 07:41:00
Here is what i have tried in bot a control source in a form and the
afterupdate box also
=[Finish Time]-[Start Time] that didnt work
=DateDiff("n",[Finish Time],[Start Time]) that didnt work that showed
12:00:00am
I also tried several different combo's of that but they didnt work

if any other info is needed please ask
bob

thank you for the help
Click to expand...
 
Back
Top