Stupid Error that I'm not seeing?

[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
ks****@alittlehelp.net
518-885-4549
Everyone Needs A Little Help!

[Bob Butler]

"TKD Karen" <ks****@alittlehelp.net> wrote in message news:<F1******************@twister.nyroc.rr.com>.. .

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?

I don't see anything obviously wrong. Your select is case-sensitive
so if you are asking for "fraud" you won't get it but other than that
it looks fine. Have you stopped the code in VB to see if you are
getting values for all 3 returned?

FWIW, I probably would have written it a little differently:

Private Function GetTotalAvg(ByVal WhichAvg As String) As Single
Dim strSQL As String
Dim rsAverages As ADODB.Recordset
Select Case lcase$(WhichAvg)
case "Admin","Fraud","Unresolved":
strSQL = "SELECT Avg(" & WhichAvg & "DaysTaken) As AvgDays " & _
"FROM qryAFIS_QtrResolDates"
Set rsAverages = CurrentProject.Connection.Execute(strSQL)
If Not rsAverages.EOF Then
If Not IsNull(rsAverages.Fields("AvgDays").Value) Then
GetTotalAvg = rsAverages.Fields("AvgDays").Value
End If
End If
Set rsAverages = Nothing
End Select
End Function

[TKD Karen]

Thanks, I'll try it your way. I was stepping through my code and the fields
in the recordset for fraud and admin were coming in as Null, but they should
have values and do when I use the sql string as a query. The function has
worked in similar situations, just not this particular one. Oh well.
Thanks for the advice!

"Bob Butler" <bu*******@earthlink.net> wrote in message
news:fa*************************@posting.google.co m...

"TKD Karen" <ks****@alittlehelp.net> wrote in message

news:<F1******************@twister.nyroc.rr.com>.. .

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?

I don't see anything obviously wrong. Your select is case-sensitive
so if you are asking for "fraud" you won't get it but other than that
it looks fine. Have you stopped the code in VB to see if you are
getting values for all 3 returned?

FWIW, I probably would have written it a little differently:

Private Function GetTotalAvg(ByVal WhichAvg As String) As Single
Dim strSQL As String
Dim rsAverages As ADODB.Recordset
Select Case lcase$(WhichAvg)
case "Admin","Fraud","Unresolved":
strSQL = "SELECT Avg(" & WhichAvg & "DaysTaken) As AvgDays " & _
"FROM qryAFIS_QtrResolDates"
Set rsAverages = CurrentProject.Connection.Execute(strSQL)
If Not rsAverages.EOF Then
If Not IsNull(rsAverages.Fields("AvgDays").Value) Then
GetTotalAvg = rsAverages.Fields("AvgDays").Value
End If
End If
Set rsAverages = Nothing
End Select
End Function