Data Type Mismatch Error

[camidon]

Private Sub cmbEndingZone_LostFocus()
Dim MyDb As Database
Dim MySet As Recordset
Dim StrCriteria As String
Dim strParam1 As Long
Dim strParam2 As Long
Dim RecordCount As Long

Set MyDb = CurrentDb()

strParam1 = [Forms]![frm_ConsultantAverages]![cmbBeginningZone]
strParam2 = [Forms]![frm_ConsultantAverages]![cmbEndingZone]

StrCriteria = "SELECT qry_SiteVisit.ZoneID, qry_SiteVisit.SiteVisitDate BETWEEN # "
StrCriteria = StrCriteria & txtBeginningDate & " # AND # "
StrCriteria = StrCriteria & txtEndingDate & " # "
StrCriteria = StrCriteria & "FROM qry_SiteVisit "
StrCriteria = StrCriteria & "WHERE [qry_SiteVisit].[ZoneID] >= " & strParam1 & ""
StrCriteria = StrCriteria & &quot; AND [qry_SiteVisit].[ZoneID] <= &quot; & strParam2 & &quot;;&quot;

Set MySet = MyDb.OpenRecordSet(StrCriteria)
MySet.MoveLast

RecordCount = MySet.RecordCount

txtVisitsMade = RecordCount

MySet.Close
Set rst = Nothing
MyDb.Close
Set db = Nothing

End Sub

I get a data type mismatch when it get to:

Set MySet = MyDb.OpenRecordSet(StrCriteria

I know my SQL is messed up but I can't figure out how.

Thanks!

Chris AGAIN!

 

[RickSpr]

This error isn't coming from your SQL. It's probably happening because you have both DAO and ADO libraries in your References list, and ADO has a higher priority (is listed first in the references).

Both DAO and ADO have a Recordset object. Because you didn't indicate which one when you declared MySet, Access used the higher-priority one, ADO.Recordset.

But MyDB is a Database object, which only DAO has. MyDB.OpenRecordset returns an object of type DAO.Recordset. Your data type mismatch is that you're assigning a DAO.Recordset object to an ADO.Recordset variable.

If you don't actually use ADO in your application, you should remove it from your References. If you use both DAO and ADO, change your MySet declaration to DAO.Recordset. Rick Sprague

 

[camidon]

Dude! You ROCK!!

That corrected my problem. The great thing about that is that I'm NOT A GREAT PROGRAMMER but my code works!!

Thanks a TON!

Chris