asked on

Arguments are of wrong type - Recordset.find error

I am running into an error when I am trying to run a find on a recordset. It's saying there are of the wrong type or are out of acceptable range.

I suspect it's because I'm using find on a Aggregation query.

Any ideas?
JOe K.

Dim rs As New ADODB.Recordset
    
    rs.Open "SELECT PO.PO_ID, PO.ChargedTo, CCur(Sum([unitprice]*[quantity])) AS SubTotal, CStr([FileNumber] & [Unit]) AS JobNum " & _
            "FROM sysTempPO as PO INNER JOIN sysTempPO_Details as POD ON PO.PO_ID = POD.PO_ID " & _
            "GROUP BY PO.PO_ID, PO.ChargedTo, [FileNumber] & [Unit];", CurrentProject.Connection, adOpenStatic
    
    For i = startOfUsed To endOfUsed
        If wks.Cells(i, startColumn) = "" And Left(wks.Cells(i, wks.Range("_JOBNUM").Column), 2) = "16" Then
            
            rs.MoveFirst
            rs.Find "SubTotal = " & CCur(wks.Cells(i, totalsColumn)) & " AND JobNum = '" & CStr(wks.Cells(i, wks.Range("_JOBNUM").Column)) & "'", 0, adSearchForward, 0

Open in new window

EE-Arguments.jpg

omgang

ADO Find method can only be used on a single column. Your criteria expression includes multiple columns.
OM Gang

ASKER CERTIFIED SOLUTION

omgang

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jeffrey Coachman

Can you post the entire code please?
I'm confused because what you posted is not exactly the same as your screenshot

Please do not use line continuation characters (" _") in raw SQL, it only confuses matters.
First make sure that the *raw* SQL returns records *first*.
If so then, then create the recordset from the working SQL, (and worry about Line continuation characters later)
If not, ...Stop and first figure out why the SQL is not returning records...
Make sense?

So try this as the recordset first please:

rs.Open "SELECT PO.PO_ID, PO.ChargedTo, CCur(Sum([unitprice]*[quantity])) AS SubTotal, CStr([FileNumber] & [Unit]) AS JobNum FROM sysTempPO as PO INNER JOIN sysTempPO_Details as POD ON PO.PO_ID = POD.PO_ID GROUP BY PO.PO_ID, PO.ChargedTo, [FileNumber] & [Unit];", CurrentProject.Connection, adOpenStatic

Thanks

JeffCoachman

ClaudeWalker

ASKER

Sorry about the delayed response. Decided to leave my work at home this weekend. Using DAO findfirst that allowed multi-column criteria worked.

Thanks,
JOe K.