Unexpected error, description 3061, Too few parameters. Expected 2.

[akins4lyfe]

hi all,


I have a SELECT query (qryReport) based on two tables, tbl_Customer & tbl_Order, I am using this query to generate a routine calculation, of total cost and profit, from periodic sales transactions.

I also set up a form, with two textboxes and a button, for users to enter dates and click on generate button, so that totals could be viewed for any dates/period entered, on a separate text boxes (txttotals).

For Example Total Cost/profit could be calculated, for the period Jan 1, 2012 - Feb 28, 2012, when a user enters these dates, on the texboxes provided.

I would like the query to compared dates entered through the form, match that with the exact date field, within the tbl_Order, then display total sales/profit for the period/dates enterd.

I am currently running into the following terrible error, when dates have been entered and the generate button clicked,("An unexpected error has been detected, description is 3061, Too few parameters. Expected 2.").

I have posted the code running behind the generate button below, any pointers, suggestions and assistance would be appreciated.

Thank you.

SQL

SELECT Sum(Format([cost_price],'Currency')) AS TotalCost, Sum(Format([sold_price],'Currency')) AS TotalSold, Sum(Format([profit1],'Currency')) AS TotalProfit, tbl_Order.order_date
FROM tbl_Customer INNER JOIN tbl_Order ON tbl_Customer.cus_id = tbl_Order.cus_id
GROUP BY tbl_Order.order_date, tbl_Customer.cus_id, tbl_Order.cus_id
HAVING (((tbl_Order.order_date)=[Forms]![frm_view]![txtrepdate1] And (tbl_Order.order_date)=[Forms]![frm_view]![txtrepdate2]) AND ((tbl_Order.cus_id)=[tbl_Customer]![cus_id]) AND ((DatePart('m',[order_date]))=7));

 

[dhookom]

Are you creating a recordset from the query? Why are you comparing Order_Date to different text boxes?

Duane
Hook'D on Access
MS Access MVP

 

[akins4lyfe]

hi, yes I am opening a record set from the query, in order to view calculated totals, based on dates. e.g monthly total. my recordset is below:

Dim rstReport as DAO.Recordset
Set rstReport=dbase.Openrecordset("qryReport")
rstReport.Findfirst("[order_date]='"& me.txtdate &"'")
If Not rstReport.Nomatch then

Me.txtTotal1=rstReport("cost_price")
Me.txtTotal2=rstReport("sold_price")
Me.txtTotal3=rstReport("profit")

Else
Msgbox"totals unavailable for dates entered",vbokOnly,"Report"
End If

It should be pretty simple procedure, except for this annoying error, which pop up all the time.

 

[dhookom]

I am not sure a recordset is required but you haven't provided emough information about your calculations.

There is a article Everything About Using Parameters from Code that might help.

I have been creating Access apps since version 1.x and could count on one hand the number of times I have used parameters with recordsets. I typically change the SQL property of a saved query which removes the parameters.

Duane
Hook'D on Access
MS Access MVP