Need help with function

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
 
C

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
 
G

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
.
 
P

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
.
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating Time 5
Employee Time Sheet Question 5
Time Function 3
Function and Query 4
Function & Querys 1
Time calculations 1
Unresolved Issue about totaling Downtime Hours 1
How do I sum this calculated control? 3

Top