VBA error calling parameter query

[Bill Cart]

This is probally a stupid error but I can't seem to find it. As far as I can
tell the code is just like in the books but I get this
Error 13 Type Mismatch o the last line shown here. I can't see what is
causing it, the query is:

"QryMowClients"
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT MowAR.CLIENTNO, MowAR.LASTNAME, MowAR.FIRSTNAME,
Sum(MowAR.TOTALMEALS) AS MealTotal
FROM MowAR
WHERE (((MowAR.DATE)>[StartDate] And (MowAR.DATE)<[EndDate]))
GROUP BY MowAR.CLIENTNO, MowAR.LASTNAME, MowAR.FIRSTNAME
ORDER BY MowAR.LASTNAME, MowAR.FIRSTNAME;



Private Sub CmdDoConvert_Click()

Dim rst As Recordset
Dim qdf As QueryDef
Dim db As Database

Set db = CurrentDb

Set qdf = db.QueryDefs("QryMowClients")
qdf.Parameters("StartDate") = Forms![MainForm]![TxStartDate]
qdf.Parameters("EndDate") = Forms![MainForm]![TxEndDate]
MsgBox qdf.Parameters("StartDate"), vbOKOnly, "Test"

Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)

 

[Douglas J Steele]

You're attempting to use DAO techniques. Access 97 typically only references
DAO, so your code would work fine there. Newer versions of Access will
usually have a reference set to ADO as well as to DAO, and the ADO reference
takes precedence (since it's higher in the list).

You need to "disambiguate" your declarations to guarantee that you're
working with DAO objects. Realistically, out of the 3 objects you're
declaring, only the Recordset object exists in both the ADO and DAO models,
but there's nothing wrong with using:

Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim db As DAO.Database

(to guarantee that you get an ADO recordset, you'd use Dim rst As
ADODB.Recordset)

 

[Bill Cart]

Yes, that seems to work OK. It got me past that error. I would never have
come up with that on my own. Thanks

You're attempting to use DAO techniques. Access 97 typically only
references
DAO, so your code would work fine there. Newer versions of Access will
usually have a reference set to ADO as well as to DAO, and the ADO
reference
takes precedence (since it's higher in the list).

You need to "disambiguate" your declarations to guarantee that you're
working with DAO objects. Realistically, out of the 3 objects you're
declaring, only the Recordset object exists in both the ADO and DAO
models,
but there's nothing wrong with using:

Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim db As DAO.Database

(to guarantee that you get an ADO recordset, you'd use Dim rst As
ADODB.Recordset)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This is probally a stupid error but I can't seem to find it. As far as I can
tell the code is just like in the books but I get this
Error 13 Type Mismatch o the last line shown here. I can't see what is
causing it, the query is:

"QryMowClients"
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT MowAR.CLIENTNO, MowAR.LASTNAME, MowAR.FIRSTNAME,
Sum(MowAR.TOTALMEALS) AS MealTotal
FROM MowAR
WHERE (((MowAR.DATE)>[StartDate] And (MowAR.DATE)<[EndDate]))
GROUP BY MowAR.CLIENTNO, MowAR.LASTNAME, MowAR.FIRSTNAME
ORDER BY MowAR.LASTNAME, MowAR.FIRSTNAME;



Private Sub CmdDoConvert_Click()

Dim rst As Recordset
Dim qdf As QueryDef
Dim db As Database

Set db = CurrentDb

Set qdf = db.QueryDefs("QryMowClients")
qdf.Parameters("StartDate") = Forms![MainForm]![TxStartDate]
qdf.Parameters("EndDate") = Forms![MainForm]![TxEndDate]
MsgBox qdf.Parameters("StartDate"), vbOKOnly, "Test"

Set rst = qdf.OpenRecordset(dbOpenSnapshot, dbForwardOnly)