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
ClaudeWalkerAsked:
Who is Participating?
 
omgangConnect With a Mentor IT ManagerCommented:
Not sure how you are using this but it seems your options are to specify the criteria in the SQL statement to the recordset only returns the desirable records or a set of records you can use Find on with only a single column criteria
OR
you may be able to switch to using DAO in which case you could specify multi-column criteria with the FindFirst method.

OM Gang
0
 
omgangIT ManagerCommented:
ADO Find method can only be used on a single column.  Your criteria expression includes multiple columns.
OM Gang
0
 
Jeffrey CoachmanMIS LiasonCommented:

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
0
 
ClaudeWalkerAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.