How to add time (Minutes & Seconds) together to get total running time

[TraceyBennet]

Hello.

This is my first time in this forum so if I get things wrong please forgive me.

I need help with coming up with a way to add a series of time values (shown as Minutes and Seconds)
together and the closest example that I can think of would be the total running time for a CD,
For example:
Track 1 is 1 minute & 30 seconds long and Track 2 is 2 minutes and 45 seconds long, therefore
the combined playing time of both songs would be 4 minutes & 15 seconds.

I need this to be done on only a specific set of records as held in the CURRENT subform.
Allow me to explain further:

We have a main form called FrmOne and a subform called SubFrmOne. On FrmOne is a Catalogue
number field that is unique to, and refers to, all the items on the CURRENT form and subform thus
creating a linked or related set of records. The number of records contained in the subform can vary and
they also have thier own item number (Item 1,2,3 and so on) which is entered into a text box called TxtItem"
but they are all related to the main form by the catalogue number so refering to them is not hard.

For example:
We give the record in the main form "FrmOne" a catalogue number of AAAA, then any items entered in the
subform "SubFrmOne" would be sequentially numbered. Also the time I wish to add together is
entered into a text box called TxtTime as Minutes and Seconds.So if there are 5 Items in the subform
it would look like this:

(FrmOne) (Catalogue Number) (SubFrmOne) (TxtItem) (Time)
Current Record = AAAA <---related to 1 1:30
2 1:30
3 2:00
4 1:30
5 2:30

So each Item could be refered to as AAAA1, AAAA2 and so on. I need to add together the times
shown on the subform for all itmes related to Catalogue Number AAAA (in this case 09:00 mins)
and then display the total in a Text box on the main form called TxtTotalTime.

I dont mind if this is done with a query, function or some other means but I have tried to get
this to work but it is beyond my skills I am afraid.

Can anyone help me out with this please, I really would be most grateful.

Tracey Bennet

 

[GingerR]

i'm guessing you want to display the total time for this catalogueID on the main form?
what you will be doing is adding up the values from the underlying table.

i dont' know the names of your tables. say the name of your table holding the detail records for each CatalogueID is called tblCatalogueTimes with fields &quot;CatalogueID&quot; and &quot;Time&quot;. you will have to replace the real names of your table and fields below.

(me.CatalogueNumber refers to the text box on your FrmOne that holds the unique catalogue number)

on the main form create a text box
in the Control Source put

=dsum("[Time]","tblCatalogueTimes","[CatalogueID] = '" & me.CatalogueNumber & "'")

set the format of the text box to Short Time.

g

 

[MichaelRed]

I do not think I can get through all of the details, however a BRIEF explination may help you. All Ms. Procucts store Data/Time &quot;values&quot; in the same format, which is basically a floating point number. The number represents the number of days from 12/30/1898 as the whole number and the fractional day as the decimal value. Since you are discussing small parts of the day, I will only include that in the remainder.

Since a whole day = 1
Half a day = -.5
quarterday = 0.25

1 hour = 1/24
1 miniute = 1/ 24 * 60

So, your collection of fractional days may simply be added to arrive at the correct total time, however, as soon as you start witht he math, the type casting of date/time is 'lost', so to show your results as date/time, you need to 're-cast' (read FORMAT) it.

So, Ignoring the niceities of wheather you use a query, or some function in/on the form and the type of the seperate 'times':

MyTime = #1:30# + #1:30# + #2:00# + #1:30# + #2:30#
? Format(MyTime, &quot;Short time&quot
09:00


MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[TraceyBennet]

Hi Guys,

A big thank you to you both for taking the time to help.

GingerR:
I tried your suggestion and no luck, the text box just displays #Name?.

Michael:
Where exactly does the MyTime string you suggested go, is it a function or maybe a control source ??

I am a bit of a newbie to this so please bare with me.

Thank you again,

Tracey

 

[MichaelRed]

MyTime is just a way to illustrate the calculation. In general, I would create a procedure &quot;basMyTime&quot; with the calculation. Then -IN EACH- of the textbox AfterUpdate events, call the procedure (basMyTime). The procedure needs to have the declaration of NyTime, substitiute the textboxes for the literal times used in the example, and it should also set the cummulative time textbox to the MyTime variable. MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[GingerR]

#Name? means you have possibly a typo--you're referencing something that doesnt exist, have it typed in wrong, etc.