Problem opening a parameter query from code

[Guest]

Access 2000

Im trying to open a parameter query form VB code I've pinched the code from
http://www.mvps.org/access/queries/qry0003.htm and modified it as follows:

Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("qryStatusatdate1")
qdfParmQry("StartDate") = #1/1/2000#
Set rs = qdfParmQry.OpenRecordset()


The query I'm trying to open is as follows:

SELECT tblStatus.lngKey, tblStatus.dteChangeDate
FROM tblStatus
WHERE tblStatus.dteChangeDate<[StartDate]


The problem is I get a type mismatch error on the last line of code.

I've tried a few different things in order to solve the problem including
changing the # to " and putting a CDate() function either in the code or in
the query or setting up a date variable to hold the criteria

A couple more points... When I get this query working the date is going to
be hooked up to a text box... The query is a source table for a much larger
query but this is the only part of it that has selection criteria

 

[Guest]

Access 2000

Im trying to open a parameter query form VB code I've pinched the code from
http://www.mvps.org/access/queries/qry0003.htm and modified it as follows:

Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("qryStatusatdate1")
qdfParmQry("StartDate") = #1/1/2000#
Set rs = qdfParmQry.OpenRecordset()


The query I'm trying to open is as follows:

SELECT tblStatus.lngKey, tblStatus.dteChangeDate
FROM tblStatus
WHERE tblStatus.dteChangeDate<[StartDate]


The problem is I get a type mismatch error on the last line of code.

I've tried a few different things in order to solve the problem including
changing the # to " and putting a CDate() function either in the code or in
the query or setting up a date variable to hold the criteria

A couple more points... When I get this query working the date is going to
be hooked up to a text box... The query is a source table for a much larger
query but this is the only part of it that has selection criteria

The only possible reason I could find for this error is if dteChangeDate is
not defined as a date.

Jim C.

 

[Van T. Dinh]

Try:

Dim db As Database 'current database
Dim rs As DAO.Recordset 'holds query resultset
Dim qdfParmQry As DAO.QueryDef 'the actual query object

Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("qryStatusatdate1")
qdfParmQry.Parameters("StartDate") = #1/1/2000#
Set rs = qdfParmQry.OpenRecordset()

 

[Guest]

Jim, dteChangeDate is definitly a Date/Time field on the table

Van, I'm afraid adding the parameters property didn't make any difference


I have put the code in break mode and run the following commands in the
immediate window

?qdfParmQry.Parameters("StartDate")+1
Gives a Type Mismatch error

So the Parameters property appears to be a string regardless of the type of
data input. So I've tried changing my query as follows

SELECT tblStatus.lngKey, tblStatus.dteChangeDate
FROM tblStatus
WHERE tblStatus.dteChangeDate < CDate([StartDate])

But it still comes up with a Type Mismatch error

Anyone got anymore ideas?

 

[Van T. Dinh]

Try in the Immediate Window:

?qdfParmQry.Parameters("StartDate").Type

It should return 8 (dbDate).

If it doesn't, change the SQL String of your Query to:

PARAMETERS [StartDate] DateTime;
SELECT tblStatus.lngKey, tblStatus.dteChangeDate
FROM tblStatus
WHERE tblStatus.dteChangeDate < [StartDate];

 

[Guest]

Van,

Thanks for that but still no joy

When I initially tested the Paremeter.Type it came up as a 10

I made the changes you suggested and it now comes up as being type 8 but
still produces a Type Mismatch error when I try to open the recordset

Simon

Try in the Immediate Window:

?qdfParmQry.Parameters("StartDate").Type

It should return 8 (dbDate).

If it doesn't, change the SQL String of your Query to:

PARAMETERS [StartDate] DateTime;
SELECT tblStatus.lngKey, tblStatus.dteChangeDate
FROM tblStatus
WHERE tblStatus.dteChangeDate < [StartDate];

--
HTH
Van T. Dinh
MVP (Access)

Jim, dteChangeDate is definitly a Date/Time field on the table

Van, I'm afraid adding the parameters property didn't make any difference


I have put the code in break mode and run the following commands in the
immediate window

?qdfParmQry.Parameters("StartDate")+1
Gives a Type Mismatch error

So the Parameters property appears to be a string regardless of the type of
data input. So I've tried changing my query as follows

SELECT tblStatus.lngKey, tblStatus.dteChangeDate
FROM tblStatus
WHERE tblStatus.dteChangeDate < CDate([StartDate])

But it still comes up with a Type Mismatch error

Anyone got anymore ideas?

 

[Van T. Dinh]

Did you change the declaration of the Recordset to DAO?

Post you new code.

 

[Guest]

Sorry Van, I missed that change in your earlier post!

It seems to be working now!

Thanks for your help