minutes:seconds

[shank]

How can I format a field to hold 00:00 (minutes:seconds) ?

In the end, I will need to add them up or do math operations with them.

Can it be done in one field or do I need a field for each?

thanks

 

[John S]

Set the Format Property in the Date/Time field to "nn:ss" (without the
quotes). This should work.

 

[John W. Vinson]

How can I format a field to hold 00:00 (minutes:seconds) ?

In the end, I will need to add them up or do math operations with them.

Can it be done in one field or do I need a field for each?

thanks

For durations, I'd really recommend that you store just integer seconds (e.g.
624 would be 10 minutes 24 seconds).

You can display this value as nn:ss with an expression like

[Duration] \ 60 & ":" & Format([Duration] MOD 60, "00")

and you can do data entry on a form by having two unbound textboxes, txtMin
and txtSec; multiply the value in txtMin by 60 and add txtSec to construct the
time (say in the Form's BeforeUpdate event), and populate the textboxes in the
form's Current event similarly.

Storing durations in a Date/Time field is possible but gives all sorts of
hassles.

 

[Douglas J. Steele]

If you want to do arithmetic, you should have a single field, but it should
be a Long Integer representing total seconds. You can easily write functions
that will convert from seconds to minutes and seconds (and vice versa).

The Date data type really isn't appropriate for what you're trying to do.
Under the covers, it's an eight byte floating point number, where the
integer portion represents the date as the number of days relative to 30
Dec, 1899, and the decimal portion represents the time as a fraction of a
day. That means that you cannot really do time arithmetic that exceeds 24
hours.

 

[Ken Sheridan]

If, to store the values in seconds, you use a long integer number column with
a Required property of True (Yes) and a Defaultvalue property of 0, called
SecondsDuration say, in the table you can input and display the value in an
unbound text box, txtDuration say, on a form bound to the table as follows:

1. If the values to be entered are always less than one hours you can set
the input Mask property of the txtDuration control to:

00:00;0;_

Otherwise leave its InputMask property blank so that you can input values of
60 minutes or more. In either case leave its Format property blank.

2. In the txtDuration control's AfterUpdate event procedure put:

Me.SecondsDuration = (Left(Nz(txtDuration, "00:00"), _
InStr(Nz(txtDuration, "00:00"), ":") - 1) * 60) _
+ Right(Nz(txtDuration, "00:00"), 2)

3. In the form's Current event procedure put:

Me.txtDuration = Format(SecondsDuration \ 60, "#00") & _
":" & Format(SecondsDuration Mod 60, "00")

Note that this only works with a form in single form view, not in continuous
form view as the unbound txtDuration would show the same value in all rows in
the latter case. To do it in continuous form view, but you'd need a 'hybrid'
control made up of two superimposed combo boxes, one to edit the values, the
other to display them.

In a report you can use an unbound control with a ControlSource property of:

= Format([SecondsDuration] \ 60, "#00") & ":" & Format([SecondsDuration] Mod
60, "00")

The mathematics on the values should be done on the value of the
SecondsDuration column of course. The results can be converted back to the
minutes:seconds format using the same expression as used in the form's
Current event procedure above.

Ken Sheridan
Stafford, England