Add up specific time values on form using querys/SQL ??

[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 sql statement or a set of querys be written that will
add up a specific range of time data; EG: 1:00 + 1:20 = 2:20 and then called
from a text box on a form so that the result is displayed in the text box.

I have searched the web and access sites for simular things 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.

 

[Sawatzky]

dear accessilliter.

this statement gives you the sum of seconds which you then could pass over to a function for extracting hour and minutes and so on back again, this could also be done in the statement itself but that would make it really ugly

SELECT Sum(Hour(TimeValue([timetable]![timtext]))*3600+Minute(TimeValue([timetable]![timtext]))*60+Second(TimeValue([timetable]![timtext]))) AS seconds
FROM timetable;

you need to make it up to your needs


regards Astrid

 

[accessilliterate]

Hi Astrid,

Thank you for your suggestion which I have tried to make work over this weekend but Access2k just gives me the following error message: "The expression is typed incorrectly or is too complex to be evaluated."

Here is the SQL that I used:
SELECT Sum(Hour(TimeValue([ tblTrackInfoSub]![Length]))*3600+Minute(TimeValue([tblTrackInfoSub]![Length]))*60+Second(TimeValue([tblTrackInfoSub]![Length]))) AS seconds
FROM tblTrackInfoSub;

The table the time data is stored in is [tblTrackInfoSub] and the actual field is [Length]

Can you make any further suggestions please as I have no idea what to try next.

THank you

accessilliterate

 

[Sawatzky]

dear accesilliterate,

in the first field-reference in your sql-statement is a space in front of the table name, delete it and try again.


regards Astrid