STILL need urgent help with adding time values together PLEASE

[accessilliterate]

Hello, I am new here so if posting again upsets anyone then I apoligise but no-one helped me when I first posted and I also noticed that no-one had marked the thread. I just assume that the right people who could help me did not get to see my original post hence my reposting in the hope that they might.

My question is can a module/function be written that will add up a specific range
of time data; EG: 1:00 + 1:20 = 2:20 and 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 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.

 

[ElGuapo]

You can write a global function to calculate the total time, but what you will have to do is to calculate the minutes and seconds separately becuse access built in time functions don't work properly for what you need.

I will assume, that you won't be using hours in your syntax and that you will write 60:12 instead of 1:00:12 in your database.

Here is an example of the code, for what you would like to do:

Function sumtime(prUniqueID)
dim second as integer
dim minute as integer

dim R as recordset
Set R= currentdb.openrecordset("SELECT Sum(right(lenght,2) as SumSec, Sum(left(lenght,len(lenght)-1)) as sumMin FROM TableName WHERE prUniqueID=" & pruniqueID)

'tablename stands for the name of the table that contains data

If R.recordcount>0 then
second= R!sumsec
minute= int(R!second/60)
second= R!sumsec - minute*60
minute= minute + R!sum min
sumtime= minute & ":" & second
Else:
sumtime= "0:00"

End if

End function

With a little bit of adjustment that should do the trick!

ElGuapo

 

[JaneC]

Hi,

A slight variation on the above. Thought I'd post it as I've just spent 1/2 an hour working it out !

Set the data source for the text box on your main form to be addtimes()


Function addtimes()
Dim db As Database
Dim rst As Recordset
Dim totsec As Integer
Dim rmin As Integer
Dim rsec As Integer
Set db = CurrentDb

rmin = 0
rsec = 0
totsec = 0

' Query 1 is the datasource for the subform

Set rst = db.OpenRecordset("Query1&quot

rst.MoveFirst

While Not rst.EOF

If rst!pnum = Forms![main].pnum Then
rmin = Left(rst!ptime, InStr(rst!ptime, ":&quot - 1)
rsec = (Mid(rst!ptime, InStr(rst!ptime, ":&quot + 1))
totsec = totsec + (rmin * 60) + rsec
End If

rmin = 0
rsec = 0
rst.MoveNext

Wend
addtimes = CStr(Int(totsec / 60)) + ":" + Right(CStr(100 + (totsec - (Int(totsec / 60) * 60))), 2)

rst.Close
End Function

This will probably take longer to run than ElGuapo's code. But it does format the seconds so that 60 seconds appears as 1:00 and not 1:0 (at least on my PC!)

Jane

 

[Edimator]

I resolve a similar problem on this way:

- Set the length field as Longint type.

- Create the next function:
Public Function ShortTimeFormat(mytime) As String
Dim mymin, mysec

mymin = Fix(mytime / 60)
mysec = mytime Mod 60
ShortTimeFormat = Format(mymin, "00&quot & ":" & Format(mysec, "00&quot
End Function

- When ever you have to "show" the length field, just put on the text control =ShortTimeFormat(prjLength) and that´s all.

- When you have the field in a query with calculations, for example, a summary of length by project, just set the query in this way: select prjUniqueID,ShortTimeFormat(SUM(prjLength)) from TABLENAME group by prjUniqueID

 

[CosmoKramer]

Set up a text box in the form footer of your subform that adds up the time field. Set its format to hh:nn:ss

=Sum([length])

In your main form set up a text box with the sum field from your subform as its control source. Make sure the format for that field is the same as the subform one.