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]

 

[kpereira]

Try dSum

DSum(expr, domain[, criteria])


expr -- An expression that identifies the numeric field whose values you want to total. It can be a string expression identifying a field in a table or query, or it can be an expression that performs a calculation on data in that field. In expr, you can include the name of a field in a table, a control on a form, a constant, or a function. If expr includes a function, it can be either built-in or user-defined, but not another domain aggregate or SQL aggregate function.

domain -- A string expression identifying the set of records that constitutes the domain. It can be a table name or a query name.

criteria --An optional string expression used to restrict the range of data on which the DSum function is performed. For example, criteria is often equivalent to the WHERE clause in an SQL expression, without the word WHERE. If criteria is omitted, the DSum function evaluates expr against the entire domain. Any field that is included in criteria must also be a field in domain; otherwise, the DSum function returns a Null.

Sorry, kind of late. I just copied and pasted from the help file, but it should do what you want.

Karen [sig][/sig]

 

[MichaelRed]

Kenny,

the "DSum" noted by Karen will give you the sum, however ther are a few issues to be aware of.

The DSum results will be the simple addition of the values (single precision) of the field. I believe the various date fomats use the "decimal' portion of the values to store the fractional days. In your example, the 0:30 + 0:30 will return 1:00 AM, not one hour, but 1 o'clock! These are not the same. If - as you suggest - the values of 00:30 represent minutes, the proper format would be 0:0:30. Adding two of these in the example would result in "12:01:00 AM" again, not just 1 minute.

DSum is generally one of the slower functions in Ms. Access. If you use this A-LOT, you will not be pleased with the execution.

[sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]