How can I specify elapsed time format (such as [mm]:ss in MS Exce.

[Tangomeister]

I want to specify elapsed time format that one can find in MS Excel.
Specifically, I want to use [mm]:ss. The Access format did not allow me. Is
there an alternative?

 

[Rick B]

Elapsed time would be stored as a number in your table.

To format it on reports, queries, and forms as minutes and seconds, you'd do
something like:


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


Rick B

 

[Tim Ferguson]

I want to specify elapsed time format that one can find in MS Excel.

There is no specific data type that is designed for periods-of-time in
Access. The DateTime data type is designed to manage particular moments-
in-time, which is a bit different.

There are a number of approaches, and the best depends on your needs. You
can use:

a text field such as "23:45:30", which will sort well, be easy for the
user interface, hard to validate, but it won't do maths and
calcuations very well;

a real number (single or double) in some defined units such as hours or
seconds, so 01:30:30 could be stored as 1.50 or 5400.0 and so on. The
UI will have to do some work to make input and output user-friendly,
but validation, sorting, and maths all work well;

two integer fields (or three) for hours and minutes (and seconds),
easy to validate, fairly easy to sort and calculate, not too hard on
the user.


and so on. There are probably more alternatives too, but you get the
idea.

Hope it helps


Tim F

 

[Jamie Collins]

There is no specific data type that is designed for periods-of-time in
Access. The DateTime data type is designed to manage particular moments-
in-time, which is a bit different.

I can see only advantages in using datetime data type. What are your
reasons for it being unsuitable?

Consider this table:

CREATE TABLE MyTestTable
(MyDateTimeCol DATETIME NOT NULL)
;

I could add data items in mm:ss like this

INSERT INTO MyTestTable
(MyDateTimeCol) VALUES (TIMESERIAL(0,555,55))
;
INSERT INTO MyTestTable
(MyDateTimeCol) VALUES (TIMESERIAL(0,44,44))
;
INSERT INTO MyTestTable
(MyDateTimeCol) VALUES (TIMESERIAL(0,3,33))
;
INSERT INTO MyTestTable
(MyDateTimeCol) VALUES (TIMESERIAL(0,0,22))
;
INSERT INTO MyTestTable
(MyDateTimeCol) VALUES (TIMESERIAL(0,0,01))
;

Needless to say adding data already datetime format (e.g. calculation
between two times) would be even more simple.

I could then get the results in the required format as:

SELECT
FORMAT(INT(MyDateTimeCol * 24 * 60), '#00')
& ':' & FORMAT(MyDateTimeCol, 'ss')
AS time_text
FROM MyTestTable
;

Jamie.

--

 

[Tim Ferguson]

(e-mail address removed) (Jamie Collins) wrote in

I can see only advantages in using datetime data type. What are your
reasons for it being unsuitable?

It fails badly for durations greater than one. The OP mentioned the Excel
format [mm]:ss which explicitly supports numbers of minutes greater
than sixty, and [hh]:mm:ss which goes beyond 24.

Try this:

debug.print CDate(36/24 + 40/24/60 + 25/24/60/60)
31/12/1899 12:40:25

and there is no way to get 36:40:25 without hacking out the individual
datepart()s -- Access just does not support the [hh] notation.

The short answer is that you _can_ do anything, but in general it leads
to an easier life to use the tools in the way they were designed.

All the best


Tim F

 

[Jamie Collins]

I can see only advantages in using datetime data type. What are your
reasons for it being unsuitable?

It fails badly for durations greater than one. The OP mentioned the Excel
format [mm]:ss which explicitly supports numbers of minutes greater
than sixty, and [hh]:mm:ss which goes beyond 24.

Try this:

debug.print CDate(36/24 + 40/24/60 + 25/24/60/60)
31/12/1899 12:40:25

First, the OP only mentioned [mm]:ss. No one mentioned [hh]:mm:ss
until you did in your last post.

Second, did you try my code with your example?

debug.print CDate(36/24 + 40/24/60 + 25/24/60/60)
31/12/1899 12:40:25

But the OP is using minutes and seconds, so the equivalent is:

debug.print Timeserial(0, 2200, 25)
31/12/1899 12:40:25

Using this in my SQL:

INSERT INTO MyTestTable
(MyDateTimeCol) VALUES (TIMESERIAL(0,2200,25))
;

Using my SELECT query (which in retrospect could be improved upon but
never mind), it returns time_text = 2200:25. QED.

The short answer is that you _can_ do anything, but in general it leads
to an easier life to use the tools in the way they were designed.

This was *my* point i.e. store date/time as datetime and use text for
front end formatting. Aren't *those* the correct tools?

Jamie.

--

 

[Tim Ferguson]

(e-mail address removed) (Jamie Collins) wrote in

store date/time as datetime and use text for
front end formatting. Aren't *those* the correct tools?

Using a DateTime value for a duration is not appropriate, no. As I said,
and as your example demonstrated, it is possible to hack out the individual
parts and format them: we are not disagreeing on that.

B Wishes


Tim F

 

[Jamie Collins]

Using a DateTime value for a duration is not appropriate, no.

I admit my original suggestion of:

CREATE TABLE MyTest (
duration DATETIME NOT NULL,
CHECK (duration >= 0)
);

was a lazy contraction of

CREATE TABLE MyTest2 (
start_date DATETIME DEFAULT 0 NOT NULL,
end_date DATETIME NOT NULL,
CHECK (start_date <= end_date)
);

i.e. I *assumed* a zero start value rather than modelling it. The
'dutation' value can now go into end_date.

An alternative (e.g. any of yours) that models time as discrete values
rather than in continuum will be a 'hack'.

As I said,
and as your example demonstrated, it is possible to hack out the individual
parts and format them

I 'hacked' out the minutes and seconds as a demonstration. In reality,
I'd do the formatting in the front end, which is very easy because the
value is already in DATETIME format.

Jamie.

--