vba code filtering top n values by group

sxxgupta used Ask the Experts™
So the coding below works if I use one make table query to make a table and then use a second select query that calls on the make table to find the top n values via the function defined below.  However, I am now trying to skip the making table part and use the criteria defined in the first select query (the query that made the table before) and use the second query that now queries on the first query to find the top N records by group.  The coding is working all the way til it faults out on the line:

 Set rs = rs2.OpenRecordset

where I get a query syntax error.  I did not have this problem before when querying the temp make table.  So I am assuming it has to do with the way I am filtering the recordset...........The coding below is not mine and was developed by an expert in prior postings.  Could use some help
Function Nth4000(GroupID, N)
    ' Returns the Nth Item in GroupID for use as a Top N per group
    ' query criteria.
    Static LastGroupId, LastNth4000InGroup
    Dim ItemName, GroupIDName, GDC, SearchTable
    Dim SQL As String, rs As DAO.Recordset, rs1 As DAO.Recordset, rs2 As DAO.Recordset, db As DAO.Database, qdf1 As DAO.QueryDef, qdf2 As DAO.QueryDef, prm As DAO.Parameter
    Dim qry1 As String
    Dim qry2 As String

    qry1 = "z nmdf servicedata_4000_a"
    qry2 = "z nmdf servicedata_4000_b"
    Set db = CurrentDb
    Set qdf1 = db.QueryDefs(qry1)
    Set qdf2 = db.QueryDefs(qry2)
    For Each prm In qdf1.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rs1 = qdf1.OpenRecordset(dbOpenDynaset)

    If (LastGroupId = GroupID) Then
        ' Returned saved result if function is called with the
        ' same GroupID more than once in a row.
        Nth4000 = LastNth4000InGroup
        ' Set the SQL statement parameters. These are the only items
        ' that need to be customized in this function.
        ' Set to Item field name.
        ItemName = "Qty"
        ' Set to Group ID field name.
        GroupIDName = "PartNo1"
        ' GroupID Delimiter Character:
        ' For Text use "'" (Note that this is a quotation mark, a space,
        ' an apostrophe, a space, and then a quotation mark. The spaces
        ' are necessary for SQL statements), Date "#", Numeric ""
        GDC = "'"
        ' Set to search table.
        SearchTable = qry2
        ' Build a Top N SQL statement dynamically given N and
        ' GroupID as parameters. Note that the sort is by the
        ' item in descending order, in order to get the Top N
        ' largest items.
        SQL = "Select Top " & N & " [" & ItemName & "] "
        SQL = SQL & "From [" & SearchTable & "] "
        SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC _
        & " "
        SQL = SQL & "Order By [" & ItemName & "] Desc"
        ' Open up recordset on Top N SQL statement and read the
        ' last record to get the smallest item in the Top N.
        Set db = CurrentDb()
        Set rs2 = qdf2.OpenRecordset
        rs2.filter = SQL
        Set rs = rs2.OpenRecordset
        If (rs.BOF And rs.EOF) Then
            ' No matches found, return a null.
            LastNth4000InGroup = Null
            LastGroupId = GroupID
            Nth4000 = LastNth4000InGroup
            ' Return the smallest Top N item in the group.
            LastNth4000InGroup = rs(ItemName)
            LastGroupId = GroupID
            Nth4000 = LastNth4000InGroup
        End If
    End If

End Function

Open in new window

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


I have two parameters:  [Forms].[z filter].[BeginDate] and [Forms].[z filter].[EndDate] that are evaluated in qry1.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

I can't say that I understand all you have going on there, but in just looking areound where you say the error occurs, ...
...It looks like you are not setting the Source (Table/Query/SQL) argument for the OpenRecordset Method

Set rs = rs2.OpenRecordset(?????)


Double click the query "z nmdf servicedata_4000_final_Method1" in the attached db...........

I am supposed to see the top 5 Qty values grouped by PartNo1
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples


I cleaned up the coding behind the form for debug purposes so it does not get bothersome....................see attached.


The coding was taken from:


I need "z nmdf servicedata_4000_a" select query to run first, then the "z nmdf servicedata_4000_b" select query, then "z nmdf servicedata_4000_final_Method1" select query that shows the top 5 Qty values grouped by PartNo1 based on the vba function



Any thoughts so far......
Hello sxxgupta

It's not easy to modify someone else's code. And it's not easy for us to guess what portions you have edited from the working previous version the one with a temp table).

Anyway, what the code currently does is about this:

21: open a dynaset from a query (z...a) after evaluating the parameters, which you explain in your first comment.

44-49: create a "Top N" query based on another query (z...b) in the string SQL

54: open the second query as dynaset (without parameter evaluation)
55: use the a full SQL query as filter (!)
56: attempt to open a new dynaset with a wrong filter

65-66: return a value from the Nth record in the query...

Notice that the first dynaset is never used. I can't reconstruct why it was there in the original (presumably working) version.

The SQL string is used as a filter, on line 55, which cannot work as is. Perhaps you intended a clause in the form "WHERE Field In (<select query>)", or more likely you want to open this newly created query as a dynaset instead of the query (z...b). For example:
    Set rs = db.OpenRecordset(SQL)

Open in new window

I see no good reason to open the rs2 dynaset, by the way. If the base query (z...b) uses parameters or is based on a sub-query using the parameters, you can't do it this way. The newly created query will need the same parameters. You might need something like this:
    With db.CreateQueryDef("", SQL)
        For Each prm In .Parameters
            prm.Value = Eval(prm.Name)
        Next prm
        Set rs = .OpenRecordset
    End With

Open in new window

Again, the code does not use either rs1 or rs1 at all. You only want to create, run, and examine a dynamically created query from a function. Try to simplify it to the bare minimum until it works. Alternatively, create your function from scratch instead of adapting this one.

Good luck!

PS: I normally don't open attached databases; the above is based only on the code in the question.


Thx Harfang.  Your suggestion worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial