#Value! Error in a vba function to return a recordset

Question

I wrote the below vba function aiming to return a SQL record set into a cell. I get #Value error when I use this function. It should get two arguments, namely two cells, and simply return Yes or No. I checked the SQL query and it works fine when written as a subroutine. So pardon for replacing some sql commands with *

Public Function TrRe(No1 As String, No2 As String) As ADODB.Recordset
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim SourceText As String

Set cn = New ADODB.Connection
cn.ConnectionString = _
    "Provider=MSOLEDBSQL;Server=****;Database=*****;Integrated Security=SSPI"
cn.Open
Set rs = New ADODB.Recordset
SourceText = " SELECT  " & _
            "   CASE  " & _
            "   WHEN ***** LIKE 'TEST%' OR *****=1 THEN 'Yes' " & _
            "   ELSE 'No " & _
            "   END " & _
            "  FROM *** "
            " WHERE ****=" & No1 & _
            "   AND ****=" & No2  
rs.ActiveConnection = cn
rs.Source = SourceText
rs.CursorType = adOpenForwardOnly
rs.LockType = adLockReadOnly
rs.Open
Set TrRe= rs
rs.Close
cn.Close
Set cn = Nothing
End Function

The query always returns a 1x1 recordset and this is Yes or No. b) The code should be written as a Function since I want to let the user to choose the inputs.
I would appreciate your comments/helps.

Answer 1

             "   ELSE 'No " & _
             "   END " & _
             "  FROM *** "
             " WHERE ****=" & No1 & _
             "   AND ****=" & No2

Is that really the code? There are at least 5 apostrophe's missing, one after " ELSE 'No " and around the parameters No1 + No2 in WHERE clause; the parameters are strings.
Use the debugger to get the content of variable SourceText to see if you have valid SQL statement.

Answer 2

It is probably incorrect to return a closed recordset. If you want to return the ‘Yes’ or ‘No’, then change the return type:

Public Function TrRe(No1 As String, No2 As String) As String
   . . .
   TrRe = rs(0)
   . . .
End Function