Function and Query

  • Thread starter Thread starter Pete
  • Start date Start date
P

Pete

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

Your code modified as follows will return the total time for a specific name
(now a function argument):

Function GetPhoneTimeTotals(vName 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, strSQL As String

strSQL = "SELECT [Name], [Phone Time] FROM [Query-Monthtodate]"
strSQL = strSQL & " WHERE [Name] = ' " & vName & " ' "

Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset(strSQL)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs.Fields(1)
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

HTH,
Nikos
 
I tried it, but got error: Compile error: argument not
optional.

any ideas?
-----Original Message-----
Pete,

Your code modified as follows will return the total time for a specific name
(now a function argument):

Function GetPhoneTimeTotals(vName 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, strSQL As String

strSQL = "SELECT [Name], [Phone Time] FROM [Query- Monthtodate]"
strSQL = strSQL & " WHERE [Name] = ' " & vName & " ' "

Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset(strSQL)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs.Fields(1)
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

HTH,
Nikos

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
.


.
 
when I put GetPhoneTimeTotals() in a field in a form, it
asks for a argument. SO i put GetPhoneTimeTotals([Name]),
but it just returns 0:00 for the total time.

-----Original Message-----
Pete,

Your code modified as follows will return the total time for a specific name
(now a function argument):

Function GetPhoneTimeTotals(vName 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, strSQL As String

strSQL = "SELECT [Name], [Phone Time] FROM [Query- Monthtodate]"
strSQL = strSQL & " WHERE [Name] = ' " & vName & " ' "

Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset(strSQL)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs.Fields(1)
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

HTH,
Nikos

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
.


.
 
Pete,

GetPhoneTimeTotals([Name]) is the idea. The argument is what you put in the
parenthesis, and it has to pass a valid name to the function (as a text
string). So, assuming the name is in another control on the same form,
called, for instance, ctlName, the function should be used in the
controlsource of your textbox returning the total time as follows:

=GetPhoneTimeTotals([ctlName])

In other words, in the parenthesis you have to refrence the control
containing the name. Are you referencing it correctly?

HTH,
Nikos



when I put GetPhoneTimeTotals() in a field in a form, it
asks for a argument. SO i put GetPhoneTimeTotals([Name]),
but it just returns 0:00 for the total time.

-----Original Message-----
Pete,

Your code modified as follows will return the total time for a specific name
(now a function argument):

Function GetPhoneTimeTotals(vName 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, strSQL As String

strSQL = "SELECT [Name], [Phone Time] FROM [Query- Monthtodate]"
strSQL = strSQL & " WHERE [Name] = ' " & vName & " ' "

Set db = DBEngine.workspaces(0).databases(0)
Set rs = db.OpenRecordset(strSQL)
interval = #12:00:00 AM#
While Not rs.EOF
interval = interval + rs.Fields(1)
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

HTH,
Nikos

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
.


.
 
Back
Top