Type Mismatch in SQL query 1

[HolyRoller]

Folks,

I have a function being used to return a UPC I'm looking up in another table. When I run it, I get a "type mismatch" error. Can you help me spot the error or give me tips on how to diagnose what the problem is? I'm not sure what is "mismatching", you see. PRODID is defined as a number in the Access Table, and I've tried both LONG and DOUBLE for 'i' below

Public Function FindUPC(i As Long) As String
    Dim DB           As Database
    Dim rsQueries    As Recordset
    Dim sqlStr, fUPC As String
    Dim index        As Long
    
    Set DB = CurrentDb()
        
    sqlStr = "SELECT UPC FROM APP_PRC_PRODUCT_UPC WHERE (PRODID=" & i & ") AND (PRIMARYUPC='Y');"
                                                      
    Set rsQueries = DB.OpenRecordset(sqlStr)
    FindUPC = rsQueries!UPC
    
    rsQueries.Close
    Set DB = Nothing
    
End Function

 

[HolyRoller]

BTW, sSQL evaluates to:
SELECT UPC FROM APP_PRC_PRODUCT_UPC WHERE (PRODID=52) AND (PRIMARYUPC='Y');

When I put that into a query in Access, it runs fine. For some reason, doing it programmatically comes up with a type mis-match.

 

[HolyRoller]

Believe it or not, I fixed everything by changing the following two lines:

    Dim DB           As Database
    Dim rsQueries    As Recordset

to

    Dim DB           As DAO.Database
    Dim rsQueries    As DAO.Recordset

Do any of you have any idea why this is? I'm stumped.

 

[cmmrfrds]

Access 2000 and above defaults to an ADO recordset an other data related objects. OpenRecordset is a DAO function and the result was being passed to an ADO recordset. By prefixing the recordset object with DAO you are using the DAO recordset object.

 

[HolyRoller]

Oh, I see. In other words, the type mis-match wasn't with regard to two fields being incorrectly compared, but with the DAO-type resultset being passed back to an ADO-type object?

If your answer is yes, [editorial]Thanks for changing the context without telling me, MS! [/editorial] Or am I wrong and this is commonly known? (I'm a bit new to this...)

 

[cmmrfrds]

You are correct, it they are 2 different objects.

A lot of people don't realize the change in data object defaults between access 97 and access 2000, until they actually use a recordset for the first time. Microsoft does mention this in the documentation, but many don't understand the implication strictly by reading the documentation. Good luck and happy coding.