We help IT Professionals succeed at work.
Get Started

vba code filtering top n values by group

sxxgupta asked
Last Modified: 2013-11-27
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
This problem has been solved!
Unlock 1 Answer and 8 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE