Error 3061 Two Few Parameter Expected 2

[JimFlood7898]

I kind of know what the problem is, I have code to export a query to excel spread sheet, got the code and lessons from a site "Access Jitsu", got the code working good until I added the "Between Dates" criteria in the query. Without the date criteria it works great. I'm sure I have to add this to the SQL line, but don't know how.



This is what I have and everything works until I add the DateReceipt range: Between [Forms]![pfrm_ExpenseSelectRpt].[cboStartDate] And [Forms]![pfrm_ExpenseSelectRpt].[cboEndDate]

SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain " & "FROM qry_ReportExpenseRange " & "ORDER BY DateReceipt "

Any help would be appreciated. Not an expert by no means, construction worker, but love access, it does a lot once it's done.

[CJ_London]

try

SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain" & _
          " FROM qry_ReportExpenseRange " & _
          " WHERE DateReceipt Between " & format([Forms]![pfrm_ExpenseSelectRpt].[cboStartDate],"mm/dd/yyyy") & " AND " & format([Forms]![pfrm_ExpenseSelectRpt].[cboEndDate],"mm/dd/yyyy") & _
          " ORDER BY DateReceipt"

[JimFlood7898]

Thanks for the quick reply, put the code in and now it says: Error Number 2450=Microsft Access cannot find the refererencd form 'pfrm_ExpenseSelectRpt.
Think I need to keep the form open until after the export. Will work on it tomorrow.

[pbaldy]

You do need to keep the form open. Also, presuming the data type is date/time, you need delimiters:

SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain" & _
          " FROM qry_ReportExpenseRange " & _
          " WHERE DateReceipt Between #" & format([Forms]![pfrm_ExpenseSelectRpt].[cboStartDate],"mm/dd/yyyy") & "# AND #" & format([Forms]![pfrm_ExpenseSelectRpt].[cboEndDate],"mm/dd/yyyy") & _
          "# ORDER BY DateReceipt"

[CJ_London]

thanks for the correction - forgot about the #

[JimFlood7898]

Tried:

SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain" & _
" FROM qry_ReportExpenseRange " & _
" WHERE D

ateReceipt

Between

#

" & format([Forms]![pfrm_ExpenseSelectRpt].[cboStartDate],"mm/dd/yyyy") & "

#

AND

#

" & format([Forms]![pfrm_ExpenseSelectRpt].[cboEndDate],"mm/dd/yyyy") & _

"

#

ORDER BY DateReceipt"

But still comes up with Error 3061 Too Few Parameters, Expected 2

[CJ_London]

is [Forms]![pfrm_ExpenseSelectRpt] open? are the fields populated?

[JimFlood7898]

Posting the full SQL from the Query that the Sub is based on

SELECT qry_Expenses.NoOrder, qry_Expenses.DateReceipt, qry_Expenses.Amount, qry_Expenses.VenderStore, tbl_ExpenseCodes.CostCode, qry_Expenses.Description, [ProjectNumber] & " " & [ProjectTitle] AS Project, qry_Expenses.ReceiptYN, qry_Expenses.Explain, qry_Expenses.MethodPay, qry_Expenses.SelectReceipt
FROM tbl_ExpenseCodes INNER JOIN (((qry_TimeShtWk INNER JOIN qry_TimeShtProj ON qry_TimeShtWk.TimeShtWk_ID = qry_TimeShtProj.TimeShtWk_ID) INNER JOIN qry_Projects ON qry_TimeShtProj.[Project_ID] = qry_Projects.Project_ID) INNER JOIN qry_Expenses ON qry_TimeShtProj.TimeShtProj_ID = qry_Expenses.TimeShtProj_ID) ON tbl_ExpenseCodes.CostCode_ID = qry_Expenses.CostCode
WHERE (((qry_Expenses.DateReceipt) Between [StartDate] And [EndDate]) AND ((qry_Expenses.MethodPay) Like "BIC*" Or (qry_Expenses.MethodPay) Like "Kwest*"));

Tried pasting all this in the Sub, but that doesn't work.

[JimFlood7898]

I went back and pasted the following SQL in the Sub OnClick. This works great, with all the other code to open and format the excel sheet.
But I really need the Between Date criteria.

SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain " & "FROM qry_ReportExpenseRange " & "ORDER BY DateReceipt "

[JimFlood7898]

Yes I had a popup form and left it open with a button to run the code to export.

[pbaldy]

I think the date prompts need to be changed so they come from the/a form as well.

[JimFlood7898]

"Removed the Order by and the Format date, trying to clean it and make it simply to work on it.
This is what I have:
SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain" & " FROM qry_ReportExpenseRange " & _
"WHERE DateReceipt Between #" & [Forms]![pfrm_ExpenseSelectRpt].[cboStartDate] & " # AND # " & [Forms]![pfrm_ExpenseSelectRpt].[cboEndDate]

Ran the PopUp form and got this Error message:

ErrorNumber: 3075= Syntax error in date in query expression DateReceipt Between #1/6/2020# AND # 2/17/2020

[pbaldy]

Your error points out the problems. You've got spaces between the date and the #, and no # at the end.

[JimFlood7898]

Change it to the following:
SQL = "SELECT NoOrder, DateReceipt, Amount, VenderStore, CostCode, Description, Project, ReceiptYN, Explain" & " FROM qry_ReportExpenseRange " & _
"WHERE DateReceipt Between #" & [Forms]![pfrm_ExpenseSelectRpt].[cboStartDate] & "# AND #" & [Forms]![pfrm_ExpenseSelectRpt].[cboEndDate] & "#"

Now I'm back to: Error Number3061- Too few parameters, Expected 2

[pbaldy]

Is the form open and the date combos filled in? Oh, and does the underlying query have any parameters that aren't being resolved?

I'd typically use textboxes for dates, but you know your situation.