Top N Values Per Group

[jimmy0305]

Could somebody please tell me what is the problem with my query?
I'm getting a type mismatch error and I can't find what's the problem...

In my query, I have Patient_ID, Psych_visit, & Diag_Code (see table,query & module below).

Many thanks....


---TABLES---
Diag_Med_TBL
Patient_ID (text)
Psych_visit (Date)

Diagnosis_detail_TBL
Diag_Code (text)

-----QUERY----

Field : Patient_ID
Sort : Ascending

Field : Psych_visit
Sort : Descending
Criteria : >=NthInGroup([Diag_Med_TBL].[Patient_ID],5)

Field : Diag_Code

----- MODULE-------
Option Compare Database

Function NthInGroup(GroupID, N)
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As Database

If (LastGroupId = GroupID) Then

NthInGroup = LastNthInGroup
Else

ItemName = "Psych_visit"

GroupIDName = "Patient_ID"

GDC = "'"

SearchTable = "Diag_Med_TBL"

SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC & " "
SQL = SQL & "Order By [" & ItemName & "] Desc"

Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then

LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else

rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If

End Function

Jimmy

 

[Jon K]

In the function, specifies the Recordset and Database as DAO objects:-

Dim SQL As String, rs As DAO.Recordset, db As DAO.Database

.

 

[jimmy0305]

Bingo... Thank you, Thank you, Thank you... I really appreciate it... Jim