Customise time in MS Access?

Question

I'm designing a database for a small medical transcription company.

Basically the data stored is in the form of files in time. In the case of time an example is 23:45 which means 23 minutes and 45 seconds. They want the total minutes at the end of the day. It comes to something like 546:23 which is 546 minutes and 23 seconds (they currently use excel). With access i am not able to display more than 60 mins.

I am not even able to configure a custom time to accept time in MM:SS format.

If someone could explain in detail an easy way to allow more than 60 mins and an easy way to solve this problem. (and no i dont want to accept mins and seconds separately)

Answer 1

In any database, a time or datetime field will only accept a real, valid time. As in, between 00:00:00 to 23:59:59 (or 01:00:00 to 24:59:59 in some DBs). JET is no exception. If you want to store an AMOUNT of time, you need to store it as an integer number. You only really need to store the number of seconds, and you can calculate that into minutes and seconds later.

How you store time in the backend can be transparent to the user - they don't have any reason to know it's an integer field and not a time field. You just have to make sure you store it and display it properly.