STILL need urgent help with adding time values together PLEASE

[accessilliterate]

Hello, I am new here so if posting again upsets anyone then I apoligise but no-one helped me when I first posted and I also noticed that no-one had marked the thread. I just assume that the right people who could help me did not get to see my original post hence my reposting in the hope that they might.

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.

 

[CosmoKramer]

Set up a text box in the form footer of your subform that adds up the time field. Set its format to hh:nn:ss:

=Sum([length])

In your main form set up a text box with the sum field from your subform as its control source. Make sure the format for that field is the same as the subform one.

 

[tempclerk]

There is already a function that will do what you seem to be asking. In the text box of your main form, try DSum("[Length]","Subform Data Source Name","[ProjectUniqueID]=MainForm![ProjectUniqueID]&quot, substituting the proper names, of course. If "(formated as text)" refers to your time field, you may need to convert to an actual time first. Try the CDate function.

As for posting again, I would suggest responding to your own post to get your thread back to the top of the list instead of repeating the same information. I am personally more annoyed to find I have wasted my time responding to a post only to find that someone else has given the exact same response to the exact same post on a different forum. The Access General Discussion Forum seems to have the most traffic, if you're concerned about how many people will see the post.

 

[NebraskaGod]

What should you do if you want to display more than a 24 hour total?