The Sum of Time

[ajb_1976]

Hello, I have copied the below code from either this site or microsoft's access help pages and it is doing exactly what it should. My problem is that i need to add an extra parameter to it but i am not sure how.
Basically the code is summing the field DURATION which is a date/field in the table tbl_Data. Duration contains data in the format of hh:mm:ss and represents the length of a phone call. The code sums this perfectly but i need to add the parameter of a [MONTH] as the tbl_Data contains records over multiple periods and I only want the code to sum duration for the selected month. What do i need to do in order to achieve this......??
Many Thanks.


Function TEST()
Dim db As DAO.Database, rs As DAO.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("tbl_Data")
interval = #12:00:00 AM#

While Not rs.EOF
interval = interval + rs![Duration]
rs.MoveNext
End With
Wend

totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

TEST = totalhours & " hours and " & minutes & " minutes"

End Function

 

[ColinEssex]

If you run this function in a query you can select the month required in the query and the function will only sum on those records selected.

Col

 

[ajb_1976]

Hi Col,
Thanks for the reply but when I implement the function in a query it ignores any criteria specified by the query and just returns the total for all records in the dataset.
What am i doing wrong!!
Thanks again,
Alan

 

[ColinEssex]

Yes, its because in the function its being told to do the whole recordset.

My fault I didn't read it properly as I was rushing out to a meeting

Again thinking quickly, I would have a field on a form where the month is selected and then include that in the function - like using Format() then using a "Do - Loop" whilst the date Formats to the selected month.

Col

 

[ajb_1976]

sorry for appearing dense but could you elaborate on your last post as i am unsure on how to go about inserting the do-loop into the code.
Thanks.

 

[EMP]

.... the field DURATION which is a date/field in the table tbl_Data. Duration contains data in the format of hh:mm:ss and represents the length of a phone call.

You can't extract the date from hh:mm:ss.
Is there another field in the table that records the dates the phone calls were made?

^

 

[ajb_1976]

sorry, post should have read date/time field and it contains the length of a phone call in hours mins and secs (hh:mm:ss). I just want to be able to sum/total this field based upon one or two parameters i.e MONTH and DEPARTMENT.
Thanks

 

[Jon K]

I just want to be able to sum/total this field based upon one or two parameters i.e MONTH and DEPARTMENT.

In VBA, you can do it in one of two ways:

A) Put two combo boxes or text boxes on a form for the user to select or type the Department and Month required. Based on the user input, build a SQL Select statement in your code and open the SQL statement as a recordset instead of the whole table "tbl_Data".

B) Build and save a parameters query. In your code, open the query as a QueryDef, pass the parameter values to it and open it as a recordset instead of the whole table "tbl_Data". (You can search the VBA forum for how to pass parameter values to a query in VBA.)


Query Approach without using VBA
Since your code would return only one Sum, I would offer a query approach without involving VBA. It can simultaneously display the Sums for all the Departments/Months. (See the queries in the attached database. I have also included a parameters query.)
.

 

[ajb_1976]

Excellent.
Thank you both for all the help.
Cheers,
Alan