Stupid Error that I'm not seeing in function?

  • Thread starter Thread starter TKD Karen
  • Start date Start date
T

TKD Karen

I wrote this function (below) that is called 3 times; one for each case
scenario. But the function only works for the Unresolved, not for the other
2 cases. There doesn't seem to be anything wrong with the strSQL statement
because if I put it into an Access query design, I get results for all 3
values. But when I run it (unresolved is the last one called, btw), Admin
and Fraud are coming up Null. Any ideas what the problem is?

Private Function GetTotalAvg(strWhichAvg As String) As Single 'for ADO only
works for Unresolved, not for Admin or Fraud
Dim strSQL As String
Dim rsAverages As ADODB.Recordset
Set rsAverages = New ADODB.Recordset
strSQL = "SELECT Avg(FraudDaysTaken) as AvgOfFraudDaysTaken, " _
& "Avg(AdminDaysTaken) as AvgOfAdminDaysTaken,
Avg(UnResolvedDaysTaken) as " _
& "AvgOfUnresolvedDaysTaken FROM qryAFIS_QtrResolDates"
rsAverages.Source = strSQL
rsAverages.Open , CurrentProject.Connection

Select Case strWhichAvg
Case "Unresolved"
GetTotalAvg = Nz(rsAverages!AvgOfUnResolvedDaysTaken, 0)
Case "Fraud"
GetTotalAvg = Nz(rsAverages!AvgOfFraudDaysTaken, 0)
Case "Admin"
GetTotalAvg = Nz(rsAverages!AvgOfAdminDaysTaken, 0)
End Select

rsAverages.Close
Set rsAverages = Nothing
End Function


--
Karen Stern
A Little Help Computer Services, LLC
www.alittlehelp.net
(e-mail address removed)
518-885-4549
Everyone Needs A Little Help!
 
Karen

I have recreated the function and it works fine for me.
What leads you to believe that it is not working. Have you
stepped through the code?

Gerald Stanley MCSD
-----Original Message-----
I wrote this function (below) that is called 3 times; one for each case
scenario. But the function only works for the Unresolved, not for the other
2 cases. There doesn't seem to be anything wrong with the strSQL statement
because if I put it into an Access query design, I get results for all 3
values. But when I run it (unresolved is the last one called, btw), Admin
and Fraud are coming up Null. Any ideas what the problem is?

Private Function GetTotalAvg(strWhichAvg As String) As Single 'for ADO only
works for Unresolved, not for Admin or Fraud
Dim strSQL As String
Dim rsAverages As ADODB.Recordset
Set rsAverages = New ADODB.Recordset
strSQL = "SELECT Avg(FraudDaysTaken) as AvgOfFraudDaysTaken, " _
& "Avg(AdminDaysTaken) as AvgOfAdminDaysTaken,
Avg(UnResolvedDaysTaken) as " _
& "AvgOfUnresolvedDaysTaken FROM qryAFIS_QtrResolDates"
rsAverages.Source = strSQL
rsAverages.Open , CurrentProject.Connection

Select Case strWhichAvg
Case "Unresolved"
GetTotalAvg =
Click to expand...
Nz(rsAverages!AvgOfUnResolvedDaysTaken, 0)
Case "Fraud"
GetTotalAvg =
Click to expand...
Nz(rsAverages!AvgOfFraudDaysTaken, 0)
Case "Admin"
GetTotalAvg =
Click to expand...
Nz(rsAverages!AvgOfAdminDaysTaken, 0)
 
I wrote this function (below) that is called 3 times; one for each case
scenario. But the function only works for the Unresolved, not for the other
2 cases. There doesn't seem to be anything wrong with the strSQL statement
because if I put it into an Access query design, I get results for all 3
values. But when I run it (unresolved is the last one called, btw), Admin
and Fraud are coming up Null. Any ideas what the problem is?

Private Function GetTotalAvg(strWhichAvg As String) As Single 'for ADO only
works for Unresolved, not for Admin or Fraud
Dim strSQL As String
Dim rsAverages As ADODB.Recordset
Set rsAverages = New ADODB.Recordset
strSQL = "SELECT Avg(FraudDaysTaken) as AvgOfFraudDaysTaken, " _
& "Avg(AdminDaysTaken) as AvgOfAdminDaysTaken,
Avg(UnResolvedDaysTaken) as " _
& "AvgOfUnresolvedDaysTaken FROM qryAFIS_QtrResolDates"
rsAverages.Source = strSQL
rsAverages.Open , CurrentProject.Connection

Select Case strWhichAvg
Case "Unresolved"
GetTotalAvg = Nz(rsAverages!AvgOfUnResolvedDaysTaken, 0)
Case "Fraud"
GetTotalAvg = Nz(rsAverages!AvgOfFraudDaysTaken, 0)
Case "Admin"
GetTotalAvg = Nz(rsAverages!AvgOfAdminDaysTaken, 0)
End Select

rsAverages.Close
Set rsAverages = Nothing
End Function
Click to expand...

Have you tried setting a breakpoint at the beginning of your Select
Case and confirming the value of strWhichAvg?

Then you can step through and determine if the proper value is coming
through from your call to this procedure.

-D
 
I have tried stepping through the code. The values are null for fraud and
admin in the recordset but correct for Unresolved. The problem is that
there should be values. When I copy the sql into a query, I get all the
correct answers. The function works in other reports with a slightly
different recordset. It also works correctly as a DAO recordset. I'm
stumped.
Thanks for trying to help, guys!
Karen
 
Back
Top