Need help with function

  • Thread starter Thread starter Help
  • Start date Start date
H

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
 
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
 
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
Click to expand...
Set db = DBEngine.workspaces(0).databases(0)
strSQL = "SELECT [Query-Monthtodate].[PHONE TIME] " & _
Click to expand...
"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
.
Click to expand...
 
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
Click to expand...
Set db = DBEngine.workspaces(0).databases(0)
strSQL = "SELECT [Query-Monthtodate].[PHONE TIME] " & _
Click to expand...
"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
.
Click to expand...
.
Click to expand...
 
Back
Top