Adding up a range of time values - Im in a mess

[kgreen]

Please excuse me if I go in-depth on this but I want to give you all the facts so to save time later (no pun intended).
Adding Up A Range Of Time Values - Im In A Mess


Is there a function/query that will add up a range of cells on a sub form that contain time values in MM:SS format, for example: 00:30 + 00:30 = 01:00 (1 minute) and then display the results in a txt field on the main form.

I have a main form called [FrmTimeMain] and a text field to contain the total time called [TxtTotalTime], a subform called [SubFrmTimes] and a Ctl/Field on the subform that contains the individual times called [CtlSingleTime].

The subform, which gets its data from a table called [TblSubMain]displays records based on what ever record the user has selected in the main form and which ever of the secondry ID chosen. Now the number of records in the subform can vary depending on what set the user selects.

Each set of records has TWO ID's, MainID is a unique number and links the data in the main form to that in the subform, Example: Main form ID 01 is related to subform ID 01 and so on. Also, as I mentioned B4, there is a second ID. This splits the data in the subform even further and can be one of Eight numbers

so: Main Form ID =01 would display
subform =01 then depending on what the user selects,
anyone of 8 subsets.

subset 1 could have 18 records in it but subset 2 may only have 5.

What I need Access to do is to look at the MainID and select what ever subset of that is being displayed in the sub form and then add together all the time values in the [CtlSingleTime] and display it in the text box on my MAIN form. This total time value should then be stored in a table called [TblMain] in a ctl called [ctlTotalTime].

Also as a footnote, I am also trying and failing sadly to get access to count and display the number of records in each subset of the MainID and write that back to the [TblMain] in a control called [CtlRecCount].

I am failing terribly on all sections of this and just ending up in a right mess. Some kind of Global Function might be the way to go but I have no idea where to start.

Any hints or suggestions would be very helpful.

Thanks in advance

Kenny [sig][/sig]

 

[DMac]

Kenny,

my head hurt half way through that so I'll just give you a couple of pointers and avoid trying to understand the details. Firstly you can just add date/times after they are converted from strings e.g.

CDate(FirstTime) + CDate(SecondTime) will give you a date/time result that you can use directly. You have to use the Format function to get it looking the way you want as the default will show a date. This is confusing since as soon as your date/time adds up to more than 24 hours your field will display something like "31/12/1899 03:28:00". This is because day 1 in Access is "31/12/1899".

Secondly, it sounds like you need to use a "group by" clause to display counts, e.g.

Select SubsetName, count(*)
from tblMain
group by SubsetName

p.s. I think you may be making the form over-complex, I would imagine you could achieve a lot of what you are doing within SQL. [sig][/sig]