Add together specific time values using function/code ??

[accessilliterate]

Hello, I am new here so if i go into too much/or not enough detail, then I apoligise.

My question is can a module/function be written that will add up a specific range
of time data; EG: 1:00 + 1:20 = 2:20 and the function called from a text box
on a form so that the result is displayed in the text box.

I wanted to do it this way if possible because I will, over time, be adding
various forms to the DB that will require this function and it would save time
to have a global one rather than having to write fresh code for each text box.
OK if I have to write it fresh code each time then so be it but I will still need
help with that and I thought I would ask first.

I have searched the web and access sites for simular functions and although
I have found some (dev ash's site for one), I am unable to apply them to
my exact needs. I can learn faster and better from examples customed to my
own needs, so if anyone can help, i will be in your debt.

I have a main form called "frmProject" and a subform called "subfrmDetails".
The sub form is related to the main form by a auto number field "prjUniqueID
on a one (frmProject) to many (subfrmDetails) relationship. Each entry in the
sub form also has a unique ID (auto number) field called "CollectionID".


The lengths I need to add up are shown in this example by being in square
brackets [ ] but this is just my way of showing the fields I wish to add up
more clearly and dont actually appear in the DB.

When the record for the Boat show is selected, the items that are related to
it are displayed in both the main for and of course the subform. To clarify
which ones I mean I have placed a * next to them in this example.

Form Name = frmProject (show in form View)

Field NAmes (auto number)
PrjName prjClient prjStart prjUniqueID prjLength
Boat Show* Mr Jon Doe* 30/09/01* 1* 11:10
Air show Mrs Jane Doe 22/09/01 2


Form Name = subfrmDetails (show as a datasheet)

Field Names (Hidden field) (formated as text)
prjUniqueID CollectionID Name Length
1* 1 Boat show1.avi* [04:30]
1* 2 Boat Show2.avi* [01:00]
1* 3 Boat Show3.avi* [05:00]
1* 4 Boat Show4.avi* [00:40]
2 5 Air Show1.avi 02:30
2 6 Air Show2.avi 08:42
2 7 Air Show3.avi 14:23


From this example, you can see that if the total length of all video clips for the
Boat show would be 11:10 or 11 minutes & ten seconds and I need to have this data
shown in a text box on the main form called "prjLength"

Your help and time would be great, you have my thanks in advance.

 

[KF]

Yes, try this in the control source of a text box on your form.
=Format(Sum([YOUR TIME FIELD NAME HERE]),"h:nn:ss&quot
Good Luck,
KF

 

[accessilliterate]

Hi KF,

Thanks for the reply but it didn't work.

I placed a unbound Text box on the same form as the textbox containing the time data (its called Length) and used this string as the control source:
=Format(Sum([Length]),"h:nn:ss&quot

All I got in the text box that should contain the added times was #Error

Any suggestions please,

Regards,

accessilliterate

 

[accessilliterate]

As a first time visitor to this forum, I have to say that I am very disapointed at the level of help offered on my question.

Reading through the different Access forums and Newsgroups, questions about time seem very popular although I have yet to see a question simular to my own.

Please, If anyone can help me because this function is a BIG part of the whole point for my database and I will be forever in your debt.

Thank you in advance,

access_illiterate