How do I set time/date format in a table to show minutes:seconds

[pjjrm]

I want the recording time of a song to display in my Access 2003 database
table. I tried entering "nn:ss", but the display still reads my entry as if
it is only hours & minutes, not minutes & seconds. It also adds "AM", even
though I chose "short time".

 

[Jeff Boyce]

It sounds like you are storing something in a date/time field. If you are
trying to store a "duration", you (and Access) will have headaches. The
Access date/time field is designed to hold "point-in-time" data, not
"duration" data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Philip]

Jeff is right. Both Excel and Access don't seem to provide a good means to
do this. The workaround I had to use was setting a custom format like this:
h:mm:ss

With that, all of the times I had to enter were mostly seconds, and an
occasional minute:second value. The way you have to enter the data is
0:00:34 for 34 seconds, and 0:01:34 for 1 minute, 34 seconds. You can trim
off the h:, but you still have to type the data in the format above,
otherwise you'll get a wierd date in the field.
Note also that while you can view the cells and see the correct duration,
if you go to that cell and veiw the contents in the formula bar, it will read
something like 12:00:34 AM. Don't worry, this won't affect the formulas,
just make sure you format every field that you reference these values with
the same as the ones you type in.

That was how I did it in Excel, I'm not sure about Access, I'm thinking it
would be similar.