parameter query - form

[fedum]

I have made a parameter query with the parameters
Startdate en Enddate (type date).
If i try this query everything is ok. Then I have made a
form to ask the startdate end enddate and count the
matching records. This with a VBA-program. I get an error
13: Types do not match. Can somebody help me because this
code was working before.

Private Sub cmdTelRecords_Click()
Dim dbHuidig As Database
Dim rstMutatie As Recordset
Dim qdfMutatie As QueryDef
Dim strAantal As String

'On Error GoTo ErrHandler:
Set dbHuidig = CurrentDb
Set qdfMutatie = dbHuidig.QueryDefs("qryschattingsdatum")
Me.txtAantalRecords.SetFocus
txtAantalRecords.Text = ""
qdfMutatie.Parameters("begindatum") = CDate(txtBeginDatum)
qdfMutatie.Parameters("einddatum") = CDate(txtEinddatum)

Set rstMutatie = dfMutatie.OpenRecordset 'error !!!!!!!!!!!

If rstMutatie.RecordCount = 0 Then
MsgBox "Er zijn geen overeenstemmende records te
vinden", , "Melding"
Else
rstMutatie.MoveLast
strAantal = rstMutatie.RecordCount
Me.txtAantalRecords.SetFocus
txtAantalRecords.Text = strAantal
End If
Exit Sub
ErrHandler:
MsgBox "Er heeft zich een fout voorgedaan of er zijn
geen records aanwezig", , "Melding"

End Sub

 

[Tim Ferguson]

Secondly i don't know what is the function CDate.

Try dateValue([txt...]) instead

There is nothing wrong with CDate.. it's the standard text-to-date
conversion function.



I don't know if this is a typo or a problem with cut-and-paste, but the
variable is defined as qdfMutatie, not dfMutatie, but that may be why this
line is bombing.

Another thing is that it is a good idea to specify what type of recordset
you want: in this case a dbOpenSnapshot, dbForwardOnly is probably
suitable.

You should know that this line may work but is not reliable: to check for
an empty recordset you would do better to check rstMutatie.EOF or .BOF

Finally, since it seems the only thing this query is doing is to count the
number of records, you might consider changing it into a count() query
instead, which would be quicker and much kinder to your network
administrator.

best wishes


Tim F

 

[ESG]

I'm getting a similar issue, although mine is setup a
little differently. I pass the parameter through the
query criteria (i.e. WHERE StartDate =
forms.frmReports.PeriodStart). Works fine normally, but
as soon as I try to manipulate it through VB, it sets the
form parameter to a null value & gives me the "too
complex" error message. I can pull up the right data in
the immediate window, but for some reason the query
doesn't get it. It's weird, too, because it worked fine
last week. Any thoughts out there?

Thanks!

-----Original Message-----
I have made a parameter query with the parameters
Startdate en Enddate (type date).
If i try this query everything is ok. Then I have made a
form to ask the startdate end enddate and count the
matching records. This with a VBA-program. I get an error
13: Types do not match. Can somebody help me because this
code was working before.

Private Sub cmdTelRecords_Click()
Dim dbHuidig As Database
Dim rstMutatie As Recordset
Dim qdfMutatie As QueryDef
Dim strAantal As String

'On Error GoTo ErrHandler:
Set dbHuidig = CurrentDb
Set qdfMutatie = dbHuidig.QueryDefs("qryschattingsdatum")
Me.txtAantalRecords.SetFocus
txtAantalRecords.Text = ""
qdfMutatie.Parameters("begindatum") = CDate(txtBeginDatum)
qdfMutatie.Parameters("einddatum") = CDate(txtEinddatum)

Set rstMutatie =

dfMutatie.OpenRecordset 'error !!!!!!!!!!!

 

[Roy Goldhammer]

What type of parameters are declared on the query?

The default is text so if it is change it to Date/time.

Secondly i don't know what is the function CDate.

Try dateValue([txt...]) instead

 

[Tim Ferguson]

I pass the parameter through the
query criteria (i.e. WHERE StartDate =
forms.frmReports.PeriodStart). Works fine normally, but
as soon as I try to manipulate it through VB, it sets the
form parameter to a null value & gives me the "too
complex" error message.

It depends:

for one thing, the syntax should be using the bang operator rather than the
dot:

Forms!frmReports!PeriodStart

and it's safer to use an explicit cast or format because not everybody uses
jet-compatible dates in forms.

There's another factor, which you hint at above, that Access passes queries
through its own expression engine, and it is at that point that references
to UI elements, like controls on forms, are resolved.

If you use VB to pass the query direct to the Jet engine, for example by
using db.OpenRecordset, then the expression engine is bypassed, and the
database is left looking at a parameter it has no hope of understanding. In
this case, you are probably better off inserting the text value straight
into the SQL as a literal

"... WHERE StartDate = " & Format( _
CDate(Forms!frmReports!PeriodStart), _
"\#yyyy\-mm\-dd\#" _
)

HTH


Tim F