Parameter Data Type Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BillCo - 30 Jun 2006 09:18 GMT

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

Reply to this Message

Allen Browne - 30 Jun 2006 09:24 GMT

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

Signature

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.

> A2000, DAO 3.6 ref set, 2 Parameters in query from an unbound form.
>
[quoted text clipped - 23 lines]
> Anyone know why this is happening or how to get around it? Starting to
> lose the plot on this one!!!

Reply to this Message

BillCo - 30 Jun 2006 09:50 GMT

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

Reply to this Message