Too Few Parameters - Error Msg. 2

[Guest_imported]

I'm trying to run a report from a form. The form has two field variables named [Beginning Date] and [Ending Date]. Clicking on a command button in the form runs a report which then runs a query to be used by the report. The code for the query is listed below:

Set Rs = Db.OpenRecordset("SELECT Count([Accident Data].[City or County]) AS City
FROM [Accident Data]
HAVING
((([Accident Data].Date) Between [forms]![Dates]![Beginning Date] And [forms]![Dates]![Ending Date])
AND (([Accident Data].[City or County])='City'))&quot

If Rs.RecordCount = 0 Then
Rs.Close
Db.Close
NumAcc = 0
End If
NumAcc = Rs!City
MsgBox "NumAcc = " & NumAcc
Rs.Close

I keep getting the error message:

Runtime Error '3061'
Too Few Parameters. Expected 2.

Help isn't much help.

 

[ghubbell]

Gene, Try:

Set Rs = Db.OpenRecordset("SELECT Count([Accident Data].[City or County]) AS City
FROM [Accident Data]
HAVING
((([Accident Data].Date) Between " & [forms]![Dates]![Beginning Date] & " And " & [forms]![Dates]![Ending Date] & &quot
AND (([Accident Data].[City or County])='City'))&quot Gord
ghubbell@total.net

 

[Katerine]

Runtime error 3061 often happens when you try to reference an SQL statement which references a form field, from VBA. This problem generally goes away if you use the "Eval" function. So the SQL should translate to:

SELECT Count([Accident Data].[City or County]) AS City
FROM [Accident Data]
HAVING ((([Accident Data].Date) Between Eval("[forms]![Dates]![Beginning Date]&quot And Eval("[forms]![Dates]![Ending Date]&quot)
AND (([Accident Data].[City or County])='City'))

One suggestion: You might want to save this statement as a query and then set the recordset to the query name. If you stick SQL into code, it will inflate the database, because it creates new SQL each time it's run, and Access doesn't deallocate that space until the database is compacted.

If you don't care about that, ghubbell's solution above should also work quite well.

Hope that helps