Mismatch error when opening SQL string

  • Thread starter Thread starter Leslie Coover
  • Start date Start date
L

Leslie Coover

Why do I get a Type mismatch error concerning the line

Set rec = db.OpenRecordset(strSQL)

when I try to run the following function?

Function GT()

Dim db As Database 'current database
Dim rec As Recordset 'recordset of Scenario
Dim strSQL As String 'SQL select statement
strSQL = "SELECT Sum(tblDiv.Div) AS SumOfDiv FROM tblDiv;"
Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL)
GT = rec("SumOfDiv")
rec.Close
Set rec = Nothing

End Function

Thanks!!
 
The next thing to check is if you have a Reference set to DAO. In your VB
code window go to Tools>References. There should be a reference set to
Microsoft DAO 3.6 Object Library. If you have both that AND an ADO
reference, then you need to explicitly define which recordset type you want
created:

Dim db As DAO.Database 'current database
Dim rec As DAO.Recordset 'recordset of Scenario

Actually, it's good practice to do this anyway.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Thanks Roger, made the following changes and it works fine.

Function GT()

Dim db As DAO.Database 'current database
Dim rec As DAO.Recordset 'recordset of Scenario
Dim strSQL As String 'SQL select statement
strSQL = "SELECT Sum(tblDiv.Div) AS SumOfDiv FROM tblDiv;"
Set db = CurrentDb()
Set rec = db.OpenRecordset(strSQL)
GT = rec("SumOfDiv")
rec.Close
Set rec = Nothing

End Function

Appreciate your help!!!
 
Back
Top