| | |
|
|
|
Function and Query |
|---|
| message from Pete on 13 May 2004 |
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
|
| Nikos Yannacopoulos replied to Pete on 14 May 2004 |
Pete,
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
"Pete" <anonymous@discussions.microsoft.com> wrote in message
news:cd2c01c43939$48957510$a601280a@phx.gbl...
|
| anonymous replied to Nikos Yannacopoulos on 16 May 2004 |
I tried it, but got error: Compile error: argument not
optional.
any ideas?
for a specific name
Monthtodate]"
But i
|
| anonymous replied to Nikos Yannacopoulos on 16 May 2004 |
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.
for a specific name
Monthtodate]"
But i
|
| Nikos Yannacopoulos replied to anonymous on 17 May 2004 |
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
<anonymous@discussions.microsoft.com> wrote in message
news:dd1101c43b73$550cb330$a301280a@phx.gbl...
|
|
Archived message: Function and Query (Microsoft Access Error Message)
