VBA Access: Type mismatch 2

[MDGarcia]

Hello everyone.
I'm running the following code in a procedure and it is giving a Type mismatch message. I can't figure out why it won't accept the Select statement. I checked the code and the values are being passed as expected.
Public Function SeekFactor(Yr) As Single
Dim rst As Recordset
Dim the_sql As String
Dim yyr As String
yyr = CStr(Yr)
the_sql = "select TheFactor from schemaname_tblfactor where TheAge = '" _
& yyr & "'" & " and ThePercent = '" & txtPct & "'"
Set rst = CurrentDb.OpenRecordset(the_sql)
If Not rst.EOF Then
nFactor = rst!TheFactor
Else
nFactor = 0
End If
rst.Close
Set rst = Nothing
End Function

-> TheAge and ThePercent fields are type Text. The variables txtPct and yyr are set as type String. txtPct is a Public variable.

-> My database is in Oracle connected by DSN. The tables are linked under Tables objects.

Any suggestions?

Thanks.

 

[PHV]

Replace this:
Dim rst As Recordset
with this:
Dim rst As DAO.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya MDGarcia . . .

Another rendition:

[blue]Public Function SeekFactor(Yr) As Single
   Dim [b]db[/b] As [purple][b]DAO[/b][/purple].Database, rst As [purple][b]DAO[/b][/purple].Recordset, SQL As String
   
   Set [b]db[/b] = CurrentDb
   SQL = "SELECT TheFactor " & _
         "FROM schemaname_tblfactor " & _
         "WHERE [TheAge] = '" & CStr(Yr) & "' and " & _
               "[ThePercent] = '" & txtPct & "';"
   Set rst = [b]db[/b].OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.EOF Then
      nFactor = rst!TheFactor
   Else
      nFactor = 0
   End If
   
   Set rst = Nothing
   Set db = Nothing

End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MDGarcia]

Hello PHV and TheAceMan1.

Both solutions are correct.

Thank you very much. Stars to both of you.

Good day.