Adding a specific range of time values to get total running time 1

[Event2020]

Hi gang,

I wondered if I can impose on you for some help. I have been trying to sort this for 2 months now with out any joy, I have posted into all the Access News groups and forums that I can find and either have had no answers or the ones I did get were too complicated and beyond my skill level to understand.

My question seems simple on paper and I can do it easly in excell spreadsheet but what I want to do in Access 2000 is have a module/function that will add up a specific range of time data; EG: 1:00 + 1:20 = 2:20 (MM:SS) and have 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 main(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 (MM:SS)
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.

Please help if you can.

event2020

 

[mrule9]

Try Military time.

 

[MichaelRed]

I pretty sure I don't have your answer, as it seems all to simple -to me.

I did generate the trivial function, which returns the 'answer' you want, however it needs the arguments to be supplied in the specific format of Date/Time with Hours, minutes and seconds, as evidenced in the usage example.

Further, I have no real concept of how you would collect the several 7 various times from your db or form for the execrise. It SEEMS to me that the whold exercise is useless, as the source for the times could easily just be an aggregate query aginst the recordset. Still, the function may give you some insight not previously available.

Public Function basSumTime(ParamArray Times() As Variant) As Date

    'Michael Red 11/26/2001

    Dim Idx As Integer
    Dim dtTemp As Date

    '? Format(basSumTime(#12:04:30 AM#, #12:01:00 AM#, #12:05:00 AM#, #12:00:40 AM#), "h:m:s")
    '0:11:10


    For Idx = 0 To UBound(Times)
        dtTemp = dtTemp + Times(Idx)
    Next Idx

    basSumTime = dtTemp

End Function

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[Mossoft]

Here is another way of doing it. Assumptions are that the times are stored in text form as "MM:SS".

Function AddingTime(i_strName As String) As String

Dim rs As DAO.Recordset
Dim strSQL As String
Dim strField As String
Dim lngMinutes As Long
Dim intSeconds As Integer

strSQL = &quot;SELECT Length FROM <sourceTable> WHERE Name = '&quot; & i_strName & &quot;'&quot;

Set rs = CurrentDb.OpenRecordset(strSQL)

If Not rs.EOF Then
rs.MoveFirst
Do
strField = rs.Fields(&quot;Length&quot
intSeconds = intSeconds + Mid(strField, InStr(strField, &quot;:&quot + 1)
If intSeconds > 59 Then
lngMinutes = lngMinutes + 1
intSeconds = intSeconds - 60
End If

lngMinutes = lngMinutes + Val(strField)

rs.MoveNext

Loop Until rs.EOF
End If

rs.Close

AddingTime = lngMinutes & &quot;:&quot; & Format(intSeconds, &quot;00&quot

End Function


M

 

[Event2020]

Att: Mossoft.

Hi ya,

Thank you for your reply and code, I really appreciate it.

One question if I may - how do I get the value generated by the function into a text box on my main form?

Thank you again,

Event2020

 

[rgbanse]

This may be too simplistic for what you want but it's quick, easy and it works. To display elasped time in a text box, place the code in your form and replace the msgbox command with a me.TxtFieldName = ElaspedTime

Function CalcElaspedTime()
Dim StartTime As Date: StartTime = Now() 'Sets StartTime to now
Dim ElaspedTime As Date
Pause (67) 'Stops code from running in this case for 67 seconds
ElaspedTime = Format((Now() - StartTime), &quot;hh:nn:ss&quot
MsgBox &quot;Started at &quot; & Format(StartTime, &quot;hh:nn:ss&quot & vbCr & &quot;It is now &quot; & Format(Now(), &quot;hh:nn:ss&quot & vbCr & Format(ElaspedTime, &quot;hh:nn:ss&quot & &quot; has elasped!&quot;
End Function

Public Function Pause(DelayInSeconds As Double) As Double
'Example: Pause(3) will delay program continuation for 3 seconds
Dim PauseTime, Start, Finish, TotalTime
PauseTime = DelayInSeconds ' Sets length of Pause.
Start = timer ' Starts the delay.
Do While timer < Start + PauseTime
DoEvents ' Let other stuff run while paused.
Loop
End Function

Hope it helps
RGB

 

[Mossoft]

There are a number of ways depending on your form setup. Try setting the ControlSource of the txtTotalTime textbox to:

=AddingTime(<text control holding the name of the project>)

M