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).

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]

 

[Robertd]

Hi Kenny,

ok for the count of records

function CountOfRecords(TheID as long)

dim TempCount as variant

' Count Records
CountOfRecords = 0

TempCount = DCount
("[MainID]", "[SourceTable]", "[MainID] = " &
TheID)
if not isnull(TempCount) then
CountOfRecords = CountOfRecords
end if

end function

this function could be called after the combobox update event that selects the subform data. something like

me![CtlRecCount] = CountOfRecords(me.MainID)

you can extend the function to count the SecondID

("[MainID]", "[SourceTable]", "[MainID] = " &
TheMainID & " And [[SecondID] = " &
theSecondID)

although it might be better to make the where clause as

dim myCondition as string

myCondition = "[MainID] = "
myCondition = myCondition & TheMainID
myCondition = myCondition & " And [[SecondID] = "
myCondition = myCondition & theSecondID

TempCount = DCount("[MainID]","[SourceTable]", myCondition)

i'll have a bit of a think about the time stuff!

but the way access handles time is the integer part is the number of days and the decimal part is the number of seconds after midnight there are number of time functions see the help index under time
sometimes it's easier to do the math using your own methods
a minute is 1/24/60

HTH
Robert






[sig][/sig]