Too Few Parameters. Expected 2 ????

[Debbie37]

I am running an SQL statement on the OnClick event of a button to first restrict the recordset by selected dates then open reports based on the recordset. Everything works great except..... How can I ask the user for date parameters within that recordset? For example, [PMdue]Between [Enter a begin date] and [Enter an end date] does not work...it results in Run-Time error 3061...Too Few Parameters. Expected 2??? If I hard code a date, [PMdue]Between #3/1/2002# and #3/8/2002#, no problem, but I can't hardcode the date, it has to be user selected. I even tried Set rst = "MyQuery", which worked fine until I put the above parameters in the query, then it didn't. Does anyone know how I can do this? Here is what I am working with:

Private Sub CmdPMprocd_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset 'ProcedName only
Dim strEquipNameIDs As String

'*********************************************************
'* Create a recordset that contains only ProcedName *
'* Then loop thru the recordset to open a report based *
'* on the report name referenced by ProcedName *
'*********************************************************

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT EquipName.EquipNameID, IIf([inhsepm] Is Not Null,DateAdd('m',[pmint],[inhsepm]),IIf([vendorpm] Is Not Null,DateAdd('m',[pmint],[vendorpm]))) AS PMDue, EquipTable.ProcedName, EquipTable.EquipInactive FROM (EquipTable LEFT JOIN PMDates ON EquipTable.EquipID = PMDates.EquipID) LEFT JOIN EquipName ON EquipTable.Nomenclature = EquipName.EquipNameID WHERE(((IIf([inhsepm] Is Not Null,DateAdd("m",[pmint],[inhsepm]),IIf([vendorpm] Is Not Null,DateAdd("m",[pmint],[vendorpm])))) Between [Enter a begin date] And [Enter and end date]) AND ((EquipTable.EquipInactive)='Active'));&quot)

While Not rst.EOF

strEquipNameIDs = "([equipnameid] = " & rst![EquipNameID] & &quot AND ([ProcedName]= '" & rst!ProcedName & "')"

DoCmd.OpenReport rst!ProcedName, acPreview, , strEquipNameIDs
rst.MoveNext

Wend
End Sub

Thanks in advance!

 

[ETID]

Have you tried capturing the date ranges as strings from an input box?

then splicing those variables into your sql statement?

 

[johncb]

I assume that there is a button on a form that the user gets to press to initiate this query.

Put that button into a defined area that also includes a start and end date. Validate these dates before creating the SQL statement and executing the query.

Remember to put the dates between two #. e.g.
&quot;Select aa, bb from tblXXX WHERE adate >= #&quot; & txtStartDate & &quot;# AND bdate <= #&quot; & txtEndDate & &quot;#;&quot;

NOTE: This SQL statement is off the top of my head and my contain syntax errors, but you get the idea. The two dates come from your form and get put into the SQL statement.

HTH

John