Parameter Query - Why this not working?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Private Sub btnCreateInvoice_click
Dim db as database
Dim rsD as recordset
Dim qryD as querydef
Dim param as Parameter
Dim patientnbr as integer
Dim filterdate as date

Set db = currentdb
patientnbr = 4197
filterdate = FormatDateTime("1/1/1900", vbShortDate)
Set qryD = db.Querydefs("qryDetailQuery")
For Each param in qryD.parameters
param.value = Eval(param.name)
Next param

Set rsD = qryD.openrecordset(dbOpensnapshot)

End Sub

In the query saved in the database I have two parameters "[filterdate]" and
"[patientnbr]" .

The last line of the code is generating an error message saying it is
looking for 2 parameters. This would lead me to believe that the
Eval(Param.name) is not evaluating to the variables I have. Can anyone
help?
 
It would probably be simpler strictly to use:

Set qryD = db.Querydefs("qryDetailQuery")
qryD.parameters("patientnbr") = 4197
qryD.Parameters("filterdate") = _
FormatDateTime(#1/1/1900#, vbShortDate)
 
Thanks Doug! Am I using Eval(param.name) wrong in my code? Running it with
an extra line in the For Each loop shows Param.Name has the brackets around
it. How is Eval supposed to be used?

Douglas J. Steele said:
It would probably be simpler strictly to use:

Set qryD = db.Querydefs("qryDetailQuery")
qryD.parameters("patientnbr") = 4197
qryD.Parameters("filterdate") = _
FormatDateTime(#1/1/1900#, vbShortDate)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Private Sub btnCreateInvoice_click
Dim db as database
Dim rsD as recordset
Dim qryD as querydef
Dim param as Parameter
Dim patientnbr as integer
Dim filterdate as date

Set db = currentdb
patientnbr = 4197
filterdate = FormatDateTime("1/1/1900", vbShortDate)
Set qryD = db.Querydefs("qryDetailQuery")
For Each param in qryD.parameters
param.value = Eval(param.name)
Next param

Set rsD = qryD.openrecordset(dbOpensnapshot)

End Sub

In the query saved in the database I have two parameters "[filterdate]"
and "[patientnbr]" .

The last line of the code is generating an error message saying it is
looking for 2 parameters. This would lead me to believe that the
Eval(Param.name) is not evaluating to the variables I have. Can anyone
help?
Click to expand...
Click to expand...
 
Back
Top