Function and Query

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

Nikos Yannacopoulos

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
 
G

Guest

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
.
Click to expand...


.
Click to expand...
 
G

Guest

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
.
Click to expand...


.
Click to expand...
 
N

Nikos Yannacopoulos

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
.
Click to expand...


.
Click to expand...
Click to expand...
 

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
Function & Querys 1
Need help with function 3
Problem w/Date time function 2
Time calculations 1
Employee Time Sheet Question 5
Unresolved Issue about totaling Downtime Hours 1
Time Function 3

Top