Table - Minutes & Seconds

[Guest]

Hi

I'm writing a database to keep track on my music collection, but I'm stuck
with something.

I want to have a field in my table that records track length in minutes and
seconds, e.g. 04:32 for 4 minutes, 32 seconds.

I've tried setting up a date/time field with the format set to nn:ss, with
or without an input mask set to 00:00, but nothing seems to work. The best I
can do is to get the record saying 32:00 which isn't much use.

Any advice would be greatly appreciated. My locale is the UK if that is
relevant.

Cheers

Rob

 

[Allen Browne]

Use 2 Number type fields: one for minutes, and the other for seconds.

Set a Validation Rule on the Seconds as:
Between 0 And 59

On your form, you can display the 2 text boxes with just a colon in between,
so it looks like this:
Duration: [ ]:[ ]

On a report, you can use one text box with Control Source of:
=[Minutes] & ":" & Format([Seconds], "00")

 

[Dodo]

I want to have a field in my table that records track length in
minutes and seconds, e.g. 04:32 for 4 minutes, 32 seconds.

It works correct here when using short time notation and input mask 00:00
for that field and typing 0432 (no colons).

 

[Guest]

Dodo, your tip didn't work on my setup.

Allen, I think I'll go with your idea for the sake of my sanity
Even though they're formatted as numbers, will I be able to use them to do
sums on?
E.g. if I've got 11 tracks with the minutes and seconds for each in my
table, can I do a query to add these times up and come out with a result also
in minutes and seconds?

Thanks

Rob

 

[Allen Browne]

If you need to regularly sum them, I would personally go with just one field
to store the number of whole seconds, and then display them as minutes and
seconds with:
=[Seconds] \ 60 & Format([Seconds] Mod 60, "\:00")

The interface is a little more tricky this way: using a pair of unbound text
boxes to store the minutes and seconds, and using the AfterUpdate event to
write to result the Seconds field:
[Seconds] = Nz(60 * [txtMinutes],0) + Nz([txtSeconds],0)

If the summing is rare you could get away with using the sepearte fields,
and then calculating the seconds as:
=Sum((Nz(60 * [Minutes],0) + Nz([Seconds],0))
and then format it as above.