Module/Prgramming Help

[Guest]

I'm trying to use the following templet found on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039


The Code for the module is as follows:

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
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 = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' 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 rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


Ok, my problem is that this was written for Access 2000 and I'm working with
Acess 2003. On the "Dim SQL As String, rs As Recordset, db As DATABASE" line
when I work with the sample database Northwind, it works fine, and the object
"DATABASE" is available. When I try to move the code over to my database,
the object "database" is not available. Is there something that I can switch
this to in order to get the same results?

I've also tried using a subquery (first part of the website suggests doing
this) and it doesn't work. I always end up crashing the whole network when I
try it. I have to find another way of getting the top three Orders (or notes
in my case) without using the subquery.

 

[Wayne Morgan]

The objects mentioned are DAO objects. Access 2000 and newer user ADO by
default, but the DAO library is still available and widely used. To fix the
problem, first go into the code editor and go to Tools|References. Scroll
down to Microsoft DAO 3.6 Object Library and check it. Click Ok to exit. The
next problem is that Access scan the libraries for an object in the order
that the checked libraries are listed in the references dialog (the checked
ones will always get moved above the unchecked ones automatically when you
click ok). For example, in the statement "Dim rs As Recordset", Access will
search the libraries for an object called Recordset, but will stop looking
at the first one it finds. Well, ADO and DAO both have a Recordset object,
so this will cause a problem if it finds the wrong one first. There are two
ways around this. 1) Use the up/down arrows in the dialog to rearange the
order of the checked libraries. 2) Specify the desired library in the Dim
statement. #2 is better, because you don't have to worry about the order
getting changed later for some reason. It also makes the code easier to read
because you don't have to guess what the order of the references is, you can
see the library that's been specified.

Following #2 above, the Dim statements in the routine you posted should look
like:

Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As DAO.Recordset, db As DAO.Database


--
Wayne Morgan
MS Access MVP

I'm trying to use the following templet found on
http://support.microsoft.com/default.aspx?scid=kb;en-us;210039


The Code for the module is as follows:

Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As DATABASE

If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
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 = "OrderDate"
' Set to Group ID field name.
GroupIDName = "CustomerID"
' GroupID Delimiter Character:
' For Text use "'", Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "Orders"
' 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 rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function


Ok, my problem is that this was written for Access 2000 and I'm working
with
Acess 2003. On the "Dim SQL As String, rs As Recordset, db As DATABASE"
line
when I work with the sample database Northwind, it works fine, and the
object
"DATABASE" is available. When I try to move the code over to my database,
the object "database" is not available. Is there something that I can
switch
this to in order to get the same results?

I've also tried using a subquery (first part of the website suggests doing
this) and it doesn't work. I always end up crashing the whole network
when I
try it. I have to find another way of getting the top three Orders (or
notes
in my case) without using the subquery.

 

[John Vinson]

Is there something that I can switch
this to in order to get the same results?

Open the VBA editor and select Tools... References. If you're not
using ADO (you should know if you are), uncheck the reference to
"Microsoft ActiveX Data Objects"; in any case scroll down to the
(unchecked) reference for Microsoft DAO x.xx Object Library (pick the
highest version, 3.51 or 3.6 or whatever is there for your version)
and check it.

John W. Vinson[MVP]