Arguments are of wrong type - Recordset.find error

Posted on 2011-04-29
Last Modified: 2012-05-11
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.Find "SubTotal = " & CCur(wks.Cells(i, totalsColumn)) & " AND JobNum = '" & CStr(wks.Cells(i, wks.Range("_JOBNUM").Column)) & "'", 0, adSearchForward, 0

Open in new window

Question by:ClaudeWalker
    LVL 28

    Expert Comment

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

    Accepted Solution

    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
    you may be able to switch to using DAO in which case you could specify multi-column criteria with the FindFirst method.

    OM Gang
    LVL 74

    Expert Comment

    by: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



    Author Comment

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

    JOe K.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Join & Write a Comment

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
    Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now