• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

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
0
ClaudeWalker
Asked:
ClaudeWalker
  • 2
1 Solution
 
omgangCommented:
ADO Find method can only be used on a single column.  Your criteria expression includes multiple columns.
OM Gang
0
 
omgangCommented:
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
 
Jeffrey CoachmanCommented:

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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now