Error 3061 Too few parameters expected 1 - good tested query

[Tony Vrolyk]

I am receving the '3061 Too Few Paramters expected 1' error when trying to
run an append query from code. This code is run OnClick of a command button
on a form. The referenced control is on the same form and not null. I have
added debug.print line to show the SQL statement the results of which I then
paste into the SQL view of a test query and it runs fine from there. I don't
understand why this is coming up. Below is the code. I would appreciate any
suggestions.

Thanks
Tony


Private Sub cmdSearch_Click()
'On Error GoTo HandleErr

If Not IsNull(Me.txPayrollDate) Then
Dim db As DAO.Database, strSql As String

Set db = CurrentDb()
strSql = "INSERT INTO Placements_Payrolls ( Placement_ID, PayrollDate )
" _
& "SELECT Placements.Placement_ID, #" & Me.txPayrollDate & "# AS
PayrollDate " _
& "FROM Placements LEFT JOIN qry_Payrolls_ListByDate ON
Placements.Placement_ID = qry_Payrolls_ListByDate.Placement_ID " _
& "WHERE (((Placements.StartDate)<=#" & Me.txPayrollDate & "#) AND
((Placements.EndDate)>=#" & Me.txPayrollDate & "# Or (Placements.EndDate) Is
Null) AND ((qry_Payrolls_ListByDate.Placement_ID) Is Null))"

'The results of this debug work find in the query builder window
Debug.Print strSql

'The error occurs on this line
db.Execute strSql

End If
End Sub

 

[Douglas J. Steele]

Is it possible that you mistyped a field name: that it's, say, PlacementID,
not Placement_ID, or Payroll_Date, not PayrollDate, or something other than
StartDate or EndDate??

 

[Tony Vrolyk]

Well that isn't it. I checked - but your suggestiong lead me to realize that
the query "qry_Payrolls_ListByDate" selected from in the SQL statement has a
criteria that is pulling the value of the txPayrollDate control on the form.
It works in query builder so the syntax must be correct but when run from
code it must not like that.

Unless you have a better solution I was thinking I could just create a
function that would get that value and use the function as the criteria in
the query.

Tony

 

[Douglas J. Steele]

Yeah, when using the Execute method, the Parameters collection doesn't get
populated.

Untested, but see whether this works:

Private Sub cmdSearch_Click()
On Error GoTo HandleErr

Dim db As DAO.Database, strSql As String
Dim qdf As DAO.QueryDef, prm As DAO.Parameter


If Not IsNull(Me.txPayrollDate) Then

Set db = CurrentDb()
strSql = "INSERT INTO Placements_Payrolls " & _
"( Placement_ID, PayrollDate ) " & _
"SELECT Placements.Placement_ID, " & _
"#" & Me.txPayrollDate & "# AS PayrollDate " & _
"FROM Placements LEFT JOIN qry_Payrolls_ListByDate " & _
"ON Placements.Placement_ID = qry_Payrolls_ListByDate.Placement_ID " &
_
"WHERE (((Placements.StartDate)<=#" & Me.txPayrollDate & "#) " & _
"AND ((Placements.EndDate)>=#" & Me.txPayrollDate & "# " & _
"Or (Placements.EndDate) Is Null) " & _
"AND ((qry_Payrolls_ListByDate.Placement_ID) Is Null))"

Set qdf = db.CreateQueryDef("", strSQL)
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
qdf.Execute

Debug.Print strSql

End If

End Sub

 

[Tony Vrolyk]

That may work but in the interest of time I have already created a quick
function that gets the value of the control and then set that function as
the criteria in the appropriate query - it works.

But thanks for the kick in the right directions.

Tony