Need help with function

[Help]

Function GetPhoneTimeTotals()

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("Query-Monthtodate")
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![Phone Time]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetPhoneTimeTotals = totalhours & ":" & minutes

End Function


This function works great to add all the times together.
Problem is: within the query it has field [Name], [Phone
Time].
The function will add all the [Phone Time] together. But i
need it to total each [Phone Time] per [Name].

Any suggestions? Thanks

 

[ChrisJ]

Returning a variable number of values from a function to
its caller is quite tricky, so you have a couple of
options.
You can fill a temporary table from within the function
that the caller can access when the function terminates,
or you can call the function once for every person in the
query and return a single value per call.

How you change the function will depend on which you choose

 

[Guest]

Try this..

-----Original Message-----
Function GetPhoneTimeTotals(strName as string)

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
Dim strSQL as string

Set db = DBEngine.workspaces(0).databases(0)
strSQL = "SELECT [Query-Monthtodate].[PHONE TIME] " & _

"FROM [Query-Monthtodate] " & _
"WHERE [Query-Monthtodate].Name = '" &
strName & "'";

Set rs = db.OpenRecordset(strSQL)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![Phone Time]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetPhoneTimeTotals = totalhours & ":" & minutes

End Function


This function works great to add all the times together.
Problem is: within the query it has field [Name], [Phone
Time].
The function will add all the [Phone Time] together. But i
need it to total each [Phone Time] per [Name].

Any suggestions? Thanks
.

 

[Pete]

I tried it and get Compile error: Argument not optional.

any ideas?

-----Original Message-----
Try this..

-----Original Message-----
Function GetPhoneTimeTotals(strName as string)

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
Dim strSQL as string

Set db = DBEngine.workspaces(0).databases(0)
strSQL = "SELECT [Query-Monthtodate].[PHONE TIME] " & _

"FROM [Query-Monthtodate] " & _
"WHERE [Query-Monthtodate].Name = '" &
strName & "'";

Set rs = db.OpenRecordset(strSQL)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs![Phone Time]
rs.MoveNext
Wend
totalhours = Int(CSng(interval * 24))
totalminutes = Int(CSng(interval * 1440))
hours = totalhours Mod 24
minutes = totalminutes Mod 60

GetPhoneTimeTotals = totalhours & ":" & minutes

End Function


This function works great to add all the times together.
Problem is: within the query it has field [Name], [Phone
Time].
The function will add all the [Phone Time] together. But i
need it to total each [Phone Time] per [Name].

Any suggestions? Thanks
.

.