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("q ryWeeklyCreditC lass")

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

Set rst = qryDef.OpenReco rdset <========== 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]

Re: Parameter Data Type Problem

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(!txtStar tDate) And IsDate(!txtEndD ate) Then
strSql = "SELECT ... WHERE ([MyDate] Between " & _
Format(!txtStar tDate, strcJetDate) & " And " & _
Format(!txtEndD ate, strcJetDate) & ");"
Set rst = db.OpenRecordse t(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" <coleman.bill@g mail.com> wrote in message
news:1151655487 .978510.287550@ y41g2000cwy.goo glegroups.com.. .[color=blue]
>
> 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("q ryWeeklyCreditC lass")
>
> For Each prm In qryDef.Paramete rs
> prm = Eval(prm.Name)
> Next
>
> Set rst = qryDef.OpenReco rdset <========== 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!!![/color]

[BillCo]

Re: Parameter Data Type Problem

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!!!