Too few parameters, expecting 1

[Guest]

I have a query that is filtered by the value of a control on a form. The
query runs great from the query design view. It contains a where clause that
looks like:

WHERE DateValue(theDate) >= [Forms]![frm_MyForm]![txt_RptStarDate]

and I have a parameter that defines this parameter as a DateTime value.

The problem is that I have some code that creates a recordset from this
query, and performs some actions with that recordset. Unfortunately, the
code keeps giving me the "too few parameters" error on the line that sets the
RS. I have tried adding the Parameters declaration to the code(strSQL), but
it is still generating the error.

Dim db as dao.database
Dim rs as dao.recordset
dim strSQL as string

set db = currentdb
strSQL = "SELECT * FROM qry_myQuery"
Set rs = db.openrecordset(strSQL)

'do something here

rs.close
set rs = nothing
set db = nothing

 

[Guest]

Try and move the filter to the RecordSet


strSQL = "SELECT * FROM qry_myQuery WHERE DateValue(theDate) >= #" &
[Forms]![frm_MyForm]![txt_RptStarDate] & "#"
Set rs = db.openrecordset(strSQL)

 

[Marshall Barton]

I have a query that is filtered by the value of a control on a form. The
query runs great from the query design view. It contains a where clause that
looks like:

WHERE DateValue(theDate) >= [Forms]![frm_MyForm]![txt_RptStarDate]

and I have a parameter that defines this parameter as a DateTime value.

The problem is that I have some code that creates a recordset from this
query, and performs some actions with that recordset. Unfortunately, the
code keeps giving me the "too few parameters" error on the line that sets the
RS. I have tried adding the Parameters declaration to the code(strSQL), but
it is still generating the error.

Dim db as dao.database
Dim rs as dao.recordset
dim strSQL as string

set db = currentdb
strSQL = "SELECT * FROM qry_myQuery"
Set rs = db.openrecordset(strSQL)

'do something here

rs.close
set rs = nothing
set db = nothing

When you use VBA you do not have Access involved in
parameter resolution so you have to take care of in your
procedure:

Dim db as dao.database
Dim qdf As QueryDef
Dim rs as dao.recordset
dim strSQL as string

set db = currentdb
Set qdf = db.QueryDefs("qry_myQuery")
qdf.Parameters(0) = Eval(qdf.Parameters(0).Name)
Set rs = qdf.openrecordset()

'do something here

rs.close
set rs = nothing
Set qdf = Nothing
set db = nothing