Best Format to record time used in calc

  • Thread starter Thread starter JB
  • Start date Start date
J

JB

What is the best way to capture time so that it's
captured/entered as HH:MM:SS but it will be used in a
calculation such as: [Mass]/[RunTime]
 
What is the best way to capture time so that it's
captured/entered as HH:MM:SS but it will be used in a
calculation such as: [Mass]/[RunTime]
Click to expand...

I'd use Long Integer seconds, with some simple VBA code to parse out
hours and minutes into seconds. The simplest way would be to have four
textboxes on a form, for hours, minutes, seconds and an (invisible)
textbox bound to the duration field. You'ld need code in the
afterupdate events of the three textboxes to add up the duration, and
in the Form's Current event to parse the duration out. Post back if
you need help with the code.
 
Yes, I would very much appreciate help with the code.
Thank you.
-----Original Message-----
What is the best way to capture time so that it's
captured/entered as HH:MM:SS but it will be used in a
calculation such as: [Mass]/[RunTime]
Click to expand...

I'd use Long Integer seconds, with some simple VBA code to parse out
hours and minutes into seconds. The simplest way would be to have four
textboxes on a form, for hours, minutes, seconds and an (invisible)
textbox bound to the duration field. You'ld need code in the
afterupdate events of the three textboxes to add up the duration, and
in the Form's Current event to parse the duration out. Post back if
you need help with the code.


.
Click to expand...
 
Yes, I would very much appreciate help with the code.
Click to expand...

Ok, let's say we have textboxes txtHr, txtMin, txtSec and txtDuration;
the first three are unbound, the last is bound to a Long Integer field
named Duration (which you'll use for your calculations).

In the AfterUpdate event of each unbound textbox put:

Private Sub txtHr_AfterUpdate()
txtDuration = Me!txtHr*3600 + Me!txtMin*60 + Me!txtSec
End Sub

and similarly for txtMin and txtSec. If any of the fields is NULL the
duration will be NULL - so you may want to set the Default property of
the textboxes to 0 so the user can enter (say) 2 hours and get a
correct result.

Then in the Form's Current event:

Private Sub Form_Current()
If Not IsNull(Me![Duration]) Then
Me!txtHr = Me![Duration] \ 3600
Me!txtMin = Me![Duration] \ 60 MOD 60
Me!txtSec = Me![Duration] MOD 60
End If
End Sub

Error trapping, validation of field values (i.e. preventing the user
from entering 125 in the Min textbox) etc. is up to you...
 
Thank you very much. With a few tweaks to fit my purpose,
it works like a charm.
-----Original Message-----
Yes, I would very much appreciate help with the code.
Click to expand...

Ok, let's say we have textboxes txtHr, txtMin, txtSec and txtDuration;
the first three are unbound, the last is bound to a Long Integer field
named Duration (which you'll use for your calculations).

In the AfterUpdate event of each unbound textbox put:

Private Sub txtHr_AfterUpdate()
txtDuration = Me!txtHr*3600 + Me!txtMin*60 + Me!txtSec
End Sub

and similarly for txtMin and txtSec. If any of the fields is NULL the
duration will be NULL - so you may want to set the Default property of
the textboxes to 0 so the user can enter (say) 2 hours and get a
correct result.

Then in the Form's Current event:

Private Sub Form_Current()
If Not IsNull(Me![Duration]) Then
Me!txtHr = Me![Duration] \ 3600
Me!txtMin = Me![Duration] \ 60 MOD 60
Me!txtSec = Me![Duration] MOD 60
End If
End Sub

Error trapping, validation of field values (i.e. preventing the user
from entering 125 in the Min textbox) etc. is up to you...



.
Click to expand...
 
Back
Top