calculate

[Qamar]

I have a field which I enter a short time such as 3:13, 5:45, etc

Now i am creating a report which at the end of the report I would like to include an unbound text box, which displays the total amount of time for that field (pls note that the total will never exceeds 24 hours) What is the best formula to use in order to calculate this ?

Cheers

 

[Crispy]

Just put the field in the report footer. The control source should be =sum(YourFieldName)
and format it as Short Time

 

[Qamar]

I've tried your idea, however, i am noticing that it is not always showing me the correct time, sometimes it works and sometimes, it doesn't ... Any ideas what might be wrong please ?

Cheers

 

[Crispy]

I had a look in the Microsoft knowledge base and the problem is with the way Access stores date/time values. However it does post some code which I have adapted and seems to work.

Create a new module in your db and paste the following into it:

Function GetTimeCardTotal()
Dim db As Database, rs As Recordset
Dim totalhours As Long, totalminutes As Long
Dim days As Long, hours As Long, minutes As Long
Dim interval As Variant, j As Integer

Set db = DBEngine.Workspaces(0).Databases(0)
Set rs = db.OpenRecordset("table1")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![f1]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetTimeCardTotal = totalhours & ":" & minutes

End Function

In the above code, "Table1" represents the table or query on which your report is based, and "F1" represents your date/time field.

Once you have done this, set the recordsource of your textbox to:

=GetTimeCardTotal()

Should work, hopefully.....

 

[Pat Hartman]

I've tried your idea, however, i am noticing that it is not always showing me the correct time, sometimes it works and sometimes, it doesn't ... Any ideas what might be wrong please ?

When doesn't it work? Are you certain that the Sum() is not going over 24 hours.

One thing you should be aware of is that the date/time field is intended to be used to store a point in time. You are using it to show elapsed time. Working with elapsed time is different than working with a point in time.