Function & Querys

  • Thread starter Thread starter Pete
  • Start date Start date
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
Click to expand...
Click to expand...
 
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top