Mismatch error when opening SQL string

[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!!

 

[Roger Carlson]

The first suggestion I have is to make sure DIV is a numeric field. If it's
text, the Sum function won't work.

--
--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

 

[Leslie Coover]

Field Name = Div
Data Type = Number
Field Size = Double

 

[Roger Carlson]

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

 

[Leslie Coover]

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!!!