Query problem in VBA

[RayMilhon]

I have a query that when I run it in in the Query Editor it works perfectly. However, I need to be able to run the query in VBA. I have a report that needs to run this query using parameters supplied by the report. When I put it in VBA and set it to run I get an error message "Method 'Open' of object '_Recordset' failed" Any help would be appreciated.

[pbaldy]

It would help to see the code.

[RayMilhon]

Here it is.

Public Function StarsBenchmark(ByRef msr As String, msryear As Long) As Double
    Dim strsql As String, rs As New ADODB.Recordset
    
    strsql = "Select Beg, End from IStar_Matrix where submeasureCode = """ & _
    msr & """ and star = 5 and Measureyear = " & msryear

    rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If Not rs.EOF Then
        If Left(msr, 3) = "PCR" Then
            StarsBenchmark = rs!high
        Else
            StarsBenchmark = rs!Low
        End If
    Else
        StarsBenchmark = 0
    End If
    rs.Close
    Set rs = Nothing
End Function

[pbaldy]

I don't use ADO much, but presuming the basics are right, use this to see what the finished SQL looks like:

BaldyWeb-Immediate window

[RayMilhon]

Paul that's how I determined that the query works in the Query analyzer copied it from the immediate window to Query Analyzer and it works as expected. It's only not working in VBA. The SQL is

Select Beg, End from IStar_Matrix where submeasureCode = "ABA - BMI within measure year or prior year" and star = 5 and Measureyear = 2016

[ssanfu]

What line does the error occur?

In my testing, the SQL has fields "Beg" and "End" (note that "END" is a reserved word), but in the "IF...End IF" test, you have the lines:
StarsBenchmark = rs!high and
StarsBenchmark = rs!Low.

But "High" and "Low" are not fields in the record set, so an error is raised.

Public Function StarsBenchmark(ByRef msr As String, msryear As Long) As Double
    Dim strsql As String, rs As New ADODB.Recordset
    
    strsql = "Select Beg, End from IStar_Matrix where submeasureCode = """ & _
    msr & """ and star = 5 and Measureyear = " & msryear

    rs.Open strsql, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    If Not rs.EOF Then
        If Left(msr, 3) = "PCR" Then
            StarsBenchmark = rs!high
        Else
            StarsBenchmark = rs!Low
        End If
    Else
        StarsBenchmark = 0
    End If
    rs.Close
    Set rs = Nothing
End Function