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 Else ' 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 Else ' Return the smallest Top N item in the group. rs.MoveLast LastNth4000InGroup = rs(ItemName) LastGroupId = GroupID Nth4000 = LastNth4000InGroup End If End If End Function
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.
When asked, what has been your best career decision?
Deciding to stick with EE.
Being involved with EE helped me to grow personally and professionally.
Connect with Certified Experts to gain insight and support on specific technology challenges including:
We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE