Parameter Data Type Problem

[BillCo]

A2000, DAO 3.6 ref set, 2 Parameters in query from an unbound form.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qryDef As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qryDef = db.QueryDefs("qryWeeklyCreditClass")

For Each prm In qryDef.Parameters
prm = Eval(prm.Name)
Next

Set rst = qryDef.OpenRecordset <========== Breaks here (3464,
Data Type mismatch)

I have two *possible* reasons:
1. Query references several sub queries, each of which also reference
the form parameters
2. Parameters are dates. I've tried concatonating "#"s, and using
cdate() but no joy

Needless to say, the query runs fine normally.

Anyone know why this is happening or how to get around it? Starting to
lose the plot on this one!!!

[Allen Browne]

If you open the query in design view, are the parameters declared?
Choose Parameters on Query menu, and enter 2 rows in the dialog, e.g.:
[Forms].[Form1].[txtStartDate] Date/Time
[Forms].[Form1].[txtEndDate] Date/Time
Additionally, set the Format property of the 2 text boxes so Access knows
they are dates on the form as well.

An alternative approach is to lose the saved query and just build the SQL
string:
Dim strSql As String
Const strcJetDate = "\#mm\/dd\/yyyy\#"
With Forms!Form1
If IsDate(!txtStartDate) And IsDate(!txtEndDate) Then
strSql = "SELECT ... WHERE ([MyDate] Between " & _
Format(!txtStartDate, strcJetDate) & " And " & _
Format(!txtEndDate, strcJetDate) & ");"
Set rst = db.OpenRecordset(strSql)
...
End If
End With

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BillCo" <co**********@gmail.com> wrote in message
news:11**********************@y41g2000cwy.googlegr oups.com...

A2000, DAO 3.6 ref set, 2 Parameters in query from an unbound form.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qryDef As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qryDef = db.QueryDefs("qryWeeklyCreditClass")

For Each prm In qryDef.Parameters
prm = Eval(prm.Name)
Next

Set rst = qryDef.OpenRecordset <========== Breaks here (3464,
Data Type mismatch)

I have two *possible* reasons:
1. Query references several sub queries, each of which also reference
the form parameters
2. Parameters are dates. I've tried concatonating "#"s, and using
cdate() but no joy

Needless to say, the query runs fine normally.

Anyone know why this is happening or how to get around it? Starting to
lose the plot on this one!!!

[BillCo]

Allen, You're a F*ckin legend!!!

declairing the parameters in the queries worked a charm.
I was trying to force the parameter type with something like:

for each prm in qrydef
prm.value = eval(prm.name)
prm.type = 8
next

- but appearently dao doesnt like being told what to do with it's
parameters :/

sometimes i just get stuck thinking from one direction!!!