DAO problem

[GAllen]

Hello all,
I can't figure out why this code fails.
It's in a form module and calls a query with 2 parameters
that refer to a 2 field date range on an open form.

I seached Google Access groups, and found this code
used several times.

The Set rst = statement causes a "data type mismatch"

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim param As DAO.Parameter
Dim qdf As DAO.QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs("qryRunningDetailOutagesRaw")
For Each param In qdf.Parameters
param = Eval(param.Name)
Next param
Set rst = qdf.OpenRecordset() '****** data type mismatch ****

Thanks for your help
Greg

 

[Pieter Wijnen]

Make sure to Declare the Parameters in your Query

ie
PARAMETERS Forms!MyForm!DateFrom DateTime, Forms!MyForm!DateTo DateTime;
SELECT ...

HTH

Pieter

 

[Guest]

Greg:

The code looks fine. Try declaring the parameters in the query. Date/time
parameters are open to misinterpretation otherwise as a date in short date
format can be interpreted as an arithmetical expression rather than a
date/time value. The query would go something like this:

PARAMETERS
Forms!MyForm!txtStartDate DATETIME,
Forms!MyForm!txtEndDate DATETIME;
SELECT *
FROM MyTable
WHERE MyDate >= Forms!MyForm!txtStartDate
AND MyDate < Forms!MyForm!txtEndDate + 1;

I can't be sure this is the cause of the problem, but it’s a possibility
worth exploring and its always prudent to declare date/time parameters in any
case. BTW the above method of defining a date range is more reliable than a
BETWEEN….AND operation as it allows for rows where the MyDate column might
have a value on the last day of the range but include a non-zero time of day
element (which can easily creep in without the user knowing if precautions
are not taken to prevent it). Such a row would be missed by a BETWEEN….AND
operation.

Ken Sheridan
Stafford, England

 

[GAllen]

Ken and Pieter,
That did the trick!
Thank you very much for your help.
Greg