Function & Querys

P

Pete

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
 
W

Wayne Morgan

Is [Name] a field available on your form, if so, is it also the name of a
control on your form? You shouldn't have both items with the same name. Try
changing the textbox to txtName or something similar, just to make it
different. Also, Name is a "reserved word" and shouldn't be used as the name
of a field or control. What sort of values are in rs.Fields(1)?

You may want to try an Aggregate query, group on the Name field and Sum the
time field. Another thing to remember is that a date or time is a specific
point in time, not an interval (i.e. not 3 hours and 10 minutes between 2:00
pm and 5:10 pm. The interval is just a number. I recommend dealing with the
interval number in the smallest units used, in this case that would be
minutes. Once you've added up the minutes you can convert back to hours and
minutes if desired. Doing it this way gives you only one calculation to keep
track of and you don't have to keep adding to the hours column every 60
minutes.

--
Wayne Morgan
Microsoft Access MVP


Pete said:
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
 

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 and Query 4
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