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.

 

[cwillsh]

I did something similar several years ago and this is how I did it.
The time for each event (avi) needs to be stored in the table as a number in it's lowest common denominator (minutes) , not as a Date/Time. This makes it easy to sum. Put it on the form as an invisible control, ie 'timno'. Then use another control with the following function to display the time for each event:=" = " & Right("00" & CStr(Int([timno]/60)),2) & ":" & Right("00" & CStr([timno]-(Int([timno]/60)*60)),2) & " Minutes". FYI, I needed to display it as 00:02 if the total minutes was 2.

 

[Guest_imported]

go to

http://support.microsoft.com/support/kb/articles/q210/6/04.asp

I think this will help you. Good luck.

 

[CosmoKramer]

If I am reading this correctly and all you are trying to do is add up times from a subform and display that total on your main form, then I will repeat my previous post from your thread in the General Discussion forum:

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.

 

[tcurtis]

Don't know if this will help but here it is anyway:

About computing a total for a calculated control on a form or report

When computing a total with an aggregate function such as Sum or a domain aggregate function such as DSum, you can't use the name of a calculated control in the Sum function. You must repeat the expression in the calculated control. For example:
=Sum([Quantity]*[UnitPrice])
However, if you have a calculated field in an underlying query, for example,
=ExtendedPrice: [Quantity]*[UnitPrice]

you can use the name of that field in the Sum function, as shown here:
=Sum([ExtendedPrice])

Notes

· For information on creating a calculated field in a query, click .
· For more information on the Sum function, click .
· For more information on the DSum function, click .

 

[genomon]

Why not take a look at DateAdd, DateDiff, and maybe DatePart functions (also format)? Sounds like a simple time-picking app....