Adding Minutes and Seconds

[Guest]

Any suggestions on adding mm:ss using Date/Time (Data Type) field. As an
example I have to add the following times. Note: these are run times and
obviously not clock times. The Date/Time field allows for hh:mm

44:22 (or 44 minutes and 22 seconds)
45:30 (or 45 minutes and 22 seconds)

Problem:
The Date/Time field allows for 23:59 maximum clock time. I'm able to
display these times using a text (Data Type) field but cannot add or average
multiple entries of these times (mm:ss).

 

[Steve Schapel]

FL,

I think you mean nn:ss (mm is month).

Date/Time fields do support seconds. But for run times, it is better to
not try and use a Date/Time field.

One option is to have 2 separate Number (Integer) fields, one for the
minutes and the other for the seconds. You can set them up on a form so
it looks almost like you are entering them as an integrated entry if you
like. Then, to do your totals and everages, it is just a matter of a
calculation, for example a total might look like this...
Sum([RunMinutes])+Sum([RunSeconds])\60 & ":" & Sum([RunSeconds]) Mod 60

Alternatively, you could use the text field as you suggested, but then
to do your calculations you would need to extract the values, for example...
RunMinutes: Val(Left([YourTextField],InStr([YourTextField],":")-1))
RunSeconds: Val(Mid([YourTextField],InStr([YourTextField],":")+1))
or perhaps...
RunSeconds: Val(Right([YourTextField],2))

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "ConvertStringStuff.mdb" which (among other things)
demonstrates how to add time values stored in text fields as strings to get
around this 24 hour limitation.
--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Guest]

Hi Steve,

Thanks for the reply. I think I'll try your 1st suggestion and try 2 number
fields. Will let you know if successful. Thanks!

Hi Rodger,

I'll check your website and let you know what helpful info. I find.

Thanks!
--
FL

FL,

I think you mean nn:ss (mm is month).

Date/Time fields do support seconds. But for run times, it is better to
not try and use a Date/Time field.

One option is to have 2 separate Number (Integer) fields, one for the
minutes and the other for the seconds. You can set them up on a form so
it looks almost like you are entering them as an integrated entry if you
like. Then, to do your totals and everages, it is just a matter of a
calculation, for example a total might look like this...
Sum([RunMinutes])+Sum([RunSeconds])\60 & ":" & Sum([RunSeconds]) Mod 60

Alternatively, you could use the text field as you suggested, but then
to do your calculations you would need to extract the values, for example...
RunMinutes: Val(Left([YourTextField],InStr([YourTextField],":")-1))
RunSeconds: Val(Mid([YourTextField],InStr([YourTextField],":")+1))
or perhaps...
RunSeconds: Val(Right([YourTextField],2))

--
Steve Schapel, Microsoft Access MVP

Any suggestions on adding mm:ss using Date/Time (Data Type) field. As an
example I have to add the following times. Note: these are run times and
obviously not clock times. The Date/Time field allows for hh:mm

44:22 (or 44 minutes and 22 seconds)
45:30 (or 45 minutes and 22 seconds)

Problem:
The Date/Time field allows for 23:59 maximum clock time. I'm able to
display these times using a text (Data Type) field but cannot add or average
multiple entries of these times (mm:ss).

 

[Guest]

Hi Roger,

Reference message dated yesterday 11/1/2006.

The ConvertStringStuff.mdb does not work properly. It hangs up. Please try.

Hi Steve,

I could not get your 1st option to work. I used the following calculation
in the Control Source field of the Text Box
=Sum([MinutesFieldName])+Sum([SecondsFieldName])\60 & ":" &
Sum([econdsFieldName]) Mod 60

I'll try again today.

 

[Steve Schapel]

FL,

Works fine for me.

You seem to have a typo in "[econdsFieldName]".

Is this a textbox in the Footer section of the form?

I suppose you realise that you need to substitute [MinutesFieldName] and
[SecondsFieldName] for the actual names of these fields in your database.