Error 3061 Too few parameters expected 2. (1 Viewer)

[SJB67]

Dear All,

I hope you can help. I have been working round this problem for 2 days...

I am running the following query directly from the code:

Public Sub Test()
Dim dbs As DAO.Database
Dim MyRs As DAO.Recordset
Dim MyRsFilter As DAO.Recordset
Dim strsql As String


strsql = "SELECT Sum([quantity]*[rrp]*(1-[Salelinedisc])*(1+[vatrate])) AS Gross, Last(tblSalesHeader.CustomerID) AS LastOfCustomerID, " _
& "Last(tblSalesHeader.SalesInvDate) AS LastOfSalesInvDate, Last(tblSalesHeader.PymtMethod) AS LastOfPymtMethod, " _
& "Last(tblSalesHeader.CustomerFullName) AS LastOfCustomerFullName, tblSalesLines.SalesHeaderID, Last(tblSalesHeader.SalesInvRef) AS LastOfSalesInvRef, " _
& "Last(tblSalesHeader.TransType) AS LastOfTransType, Last(IIf([transtype]='R','Credit Note','Invoice')) AS DocDesc " _
& "FROM tblSalesHeader LEFT JOIN tblSalesLines ON tblSalesHeader.SalesInvNo = tblSalesLines.SalesHeaderID " _
& "GROUP BY tblSalesLines.SalesHeaderID;"


Set dbs = CurrentDb
Set MyRs = dbs.OpenRecordset(strsql)

With MyRs
Debug.Print strsql
.Filter = "[CustomerID]= 718"
Set MyRsFilter = MyRs.OpenRecordset
Debug.Print .RecordCount


.Close

End With
Set MyRsFilter = Nothing
Set MyRs = Nothing
Set dbs = Nothing


End Sub
If I remove the filter the query returns the correct number of records. When I apply it I get the above error message. I have tried numerous syntax with no success.

the filter on [CustomerID] should return 2 records (tested in independent query).

Any suggestions please?
:banghead:

 

[arnelgp]

on your first Aggregate query your [CustomerID] there is renamed as LastOfCustomerID, so you must filter to this field:

.With MyRs
Debug.Print strsql
.Filter = "[LastOfCustomerID]= 718"
Set MyRsFilter = MyRs.OpenRecordset
Debug.Print .RecordCount


.Close

End With

 

[spikepl]

Take great care using the SQL function Last. That function does not always do what you'd expect, see the docs. Max and Min are much more reliable.

 

[jdraw]

I agree with spike --beware of First and Last functions. Better , especially if you are working with Dates, to use Min and Max.

 

[MarkK]

Also, it will be much faster to open the exact recordset you need, rather than open a bigger recordset and filter it. Put a WHERE clause in your SQL.

Perhaps a better strategy to get at the last record in a particular sort order, is reverse sort and select the first record, like . . .

SELECT TOP 1 *
FROM Table1
WHERE Year([Date]) >= 2013
ORDER BY [Date] Desc;

See how that returns all the fields from the last record, by date, in 2013?

 

[SJB67]

Many thanks all. I am working through removing 'last' where possible.

Parameters error now gone but now filter doesn't work at all. I am now working on this but may come back with new thread is unsuccessful!

I agree with comment about where, but am trying to get simple version of query working first.