Minutes and Seconds

[Mile-O]

Why don't you set the format of your date/time field to Long Time

and use a query to recalculate the data from the hh:mm to hh:mm:ss format.

Like this attachment:

Use the update query to convert your times back and the other query will total correctly.

 

[Rich]

Yes, but who wants to keep entering 00 for the vast majority of entries?

 

[Mile-O]

Yes, but who wants to keep entering 00 for the vast majority of entries?

Hence the update query...

A Monday morning solution (I'll wipe my eyes clean later)

 

[Mile-O]

If you are typing the times into a form then don't bind the time directly to the field but put the time into an unbound control that, on its AfterUpdate puts TimeSerial(0, Hour(txtLength), Minute(txtLength)) into a hidden control bound to the field.

 

[Fornatian]

Try the attached.

 

[Rich]

Wouldn't want to appear picky here Ian but, you can't enter anything longer than 23:59 in yours or Milo's samples. The only way to deal with track lengths is to use a text field and a couple of calculated fields on the form/report

 

[Fornatian]

Not picky Rich, a good point. Good job I haven't got a real job
:uts thinking cap on::

 

[Fornatian]

Not picky Rich, a good point. Good job I haven't got a real job
:uts thinking cap on::

 

[Rich]

Trying to boost your post count Ian?

 

[downunder]

Thanks everyone for your input. I understand what you are trying to do with the samples although they seem a mite unwieldy to use as they don't reflect the actual minutes and seconds as entered by the user. I guess I was hoping for a simple function within Access to do the calculation but it appears not.

Incidentally, the time sum would never exceed 24 hours as the database is to be used for input of music tracks and the calculation of number of tracks to include on one CD which would seldom exceed 60 minutes. The actual calculation, however, has to be very accurate, down to the last second.

 

[namliam]

Think you may be off on a tangent. The problem is with elapsed minutes and seconds, not times. #7:29# to Access represents 7:29 A.M. (hours and minutes). It's stored as a portion of day. Try the following in the debug window to see why you're coming up with some strange results:

x = #7:29#
? x
7:29:00 AM
? cdbl(x)
0.311805555555556
y = #4:42#
? y
4:42:00 AM
? cdbl
0.195833333333333
? x + y
12:11:00 PM
? cdbl(x + y)
0.507638888888889

We're looking for 12 minutes and 11 seconds, but are getting what equates to 731 minutes.

Back to the drawing board!

if #7:29# comes back as hours:mins and you want Mins:secs how about trying #0:07:29# (and offcourse #0:04:42# then adding those two will (obviously) leed to 0:12:11 (12 minutes and 11 secs)

Regards

 

[Rich]

What will #0:63:32# come out as?

 

[namliam]

If you enter it like that: Nothing, error (not a valid time)

But if its a result of adding:
#0:33:04# + #0:30:00#
1:03:04

 

[Rich]

That's the problem, we're talking about track/lap times which the total or even entered time is always mins:secs

 

[Fornatian]

Rich, I find that comment a little 'rich' , actually I think there were two gamers stealing my bandwidth when I posted, so I hit the enter twice. Damn line-sharing kids.

Down Under:

Using either method you can't calculate above 23 MINUTES AND 59 SECONDS. Try the attached instead which should deal with Rich's valid point

 

[downunder]

You've got it, Fornation, works great! Thanks to you and all contributions.

 

[raskew]

What was initially posted is a math problem, pure and simple. It has nothing to do whatsoever with the ‘date/time’ data type. Gotta say that I’m disappointed with the veteran posters who got sucked into this and proceeded to reinforce the relative inexperienced programmer’s misconceptions about date/time data type (which is all about actual dates/times) and encouraged him to continue to use it versus concentrating on elapsed times, which is all about adding (as integers) days, hours, minutes and seconds.

Given the guidance provided, the poster is on a slippery slide to disaster.

How about regrouping and talking about:

(a) Converting a string representation of minutes (e.g.” 07”) to seconds (i.e. 420)
(b) Formatting an integer to a string value, (e.g. 426 = “07:06”)

 

[Fornatian]

raskew, thanks for the disappointment, that helps.

Doesn't my example adequately account for translation of minutes to seconds etc?

 

[Rich]

Rich, I find that comment a little 'rich' , actually I think there were two gamers stealing my bandwidth when I posted, so I hit the enter twice. Damn line-sharing kids.

I know the feeling

Bob you didn't offer the alternative, we're looking for your answer to the endless use of Right/Int/CDbl/Format /*60 etc etc. to arrive at the answer

 

[Mile-O]

How about regrouping and talking about:

(a) Converting a string representation of minutes (e.g.” 07”) to seconds (i.e. 420)
(b) Formatting an integer to a string value, (e.g. 426 = “07:06”)

Something, perchance, like the function I posted earlier?