elapsed time

[eddy3459]

hello all,

i have quite a unique problem regarding elapsed time . Let say i have
2 fields in a form bound to a table and 1 field as calculated field. In
1 field i have start time field, 2nd field end time field, and the 3rd
field holding the value of elapsed time between start time and end
time.

My question is how do I display elapsed duration from start time,
updated every second in the third field until some event has stopped,
and finally display total elapsed time between those times.

I have seen seen examples how to calculate elapsed time given the start
time and end time, but quite clueless on how to display running elapsed
time

Any help from you guys is greatly appreciated

thank you

 

[John Vinson]

hello all,

i have quite a unique problem regarding elapsed time . Let say i have
2 fields in a form bound to a table and 1 field as calculated field. In
1 field i have start time field, 2nd field end time field, and the 3rd
field holding the value of elapsed time between start time and end
time.

My question is how do I display elapsed duration from start time,
updated every second in the third field until some event has stopped,
and finally display total elapsed time between those times.

I have seen seen examples how to calculate elapsed time given the start
time and end time, but quite clueless on how to display running elapsed
time

You'll need to use the Form's Timer event. Set the Timer property to
1000 (milliseconds, one second); in the Timer event put code like

Private Sub Form_Timer()
If IsNull(Me!txtEndTime) Then
Me.txtDuration = DateDiff("s", Me!txtStartTime, Now())
Else
Me.txtDuration = DateDiff("s", Me!txtStartTime, Me!txtEndTime)
End If
End Sub

This will show the duration as integer seconds - if you want minutes
and seconds use the DateDiff to set an integer variable and then
display an expression like

Me.txtDuration = iDuration \ 60 & ":" & Format(iDuration MOD 60, "00")

John W. Vinson[MVP]

 

[eddy3459]

Thank you for the tips man...it works like charm.
I did some modification though to make it work like i want it to do...i
had to change the txtStartTime to TimeValue Format if not i get a
runtime error of "overflow".Here's the modification that i did:

If IsNull(Me!txtEndTime) Then

Me.txtDuration = DateDiff("n", TimeValue(Me.txtBeginTime),
TimeValue(Now())) & _
":" & Format(DateDiff("s", TimeValue(Me.txtBeginTime),
TimeValue(Now())) Mod 60, "00")

Else
Me.txtDuration = DateDiff("n", TimeValue(Me.txtBeginTime),
TimeValue(Me.txtEndTime)) & _
":" & Format(DateDiff("s", TimeValue(Me.txtBeginTime),
TimeValue(Me.txtEndTime)) Mod 60, "00")

End If

my next question is can we do same for subform. If i have a subform
dispalying a data from a certain query, can i have a calculated field
as elapsed time in datasheet view, and how do assign time interval for
subform when time interval property is only for a form?

thanx again for any reply...TQ

 

[John Vinson]

Thank you for the tips man...it works like charm.
I did some modification though to make it work like i want it to do...i
had to change the txtStartTime to TimeValue Format if not i get a
runtime error of "overflow".

ah... probably it was interpreting the time incorrectly.

Here's the modification that i did:

If IsNull(Me!txtEndTime) Then

Me.txtDuration = DateDiff("n", TimeValue(Me.txtBeginTime),
TimeValue(Now())) & _
":" & Format(DateDiff("s", TimeValue(Me.txtBeginTime),
TimeValue(Now())) Mod 60, "00")

Else
Me.txtDuration = DateDiff("n", TimeValue(Me.txtBeginTime),
TimeValue(Me.txtEndTime)) & _
":" & Format(DateDiff("s", TimeValue(Me.txtBeginTime),
TimeValue(Me.txtEndTime)) Mod 60, "00")

End If

Note that you can use the builtin function Time() instead of the
date/time function Now(), and save a step.

my next question is can we do same for subform. If i have a subform
dispalying a data from a certain query, can i have a calculated field
as elapsed time in datasheet view, and how do assign time interval for
subform when time interval property is only for a form?

All the Timer interval does is force a call to the VBA routine every
timerinterval milliseconds. That VBA routine can update a control on
the current form, on a subform, or on a completely different form if
you wish:

Me!subMySubform.Form!txtTimerInterval = <whatever>

John W. Vinson[MVP]

 

[Guest]

Is it possible to do the same type of evaluation of 2 fields using the
DateValue instead of TimeValue? And, how would you go about using that? Do
I need a date timer as well? I have a contract record containing a start
date and end date of the contract. As soon as that end date is reached (need
to check end date against the system date??), I need to update the status to
"Closed" and then the new contract record takes effect with an extended new
start and end date, with a new status of "Active." This is way complicated
for me. Any help will be great. Thank you .