Mendip Data Systems

Database applications for businesses and schools



Public Function Serialize(qryname As String, KeyName As String, keyValue) As Long


On Error GoTo Err_Handler


   'used to create rank order for records in a query

   'add as query field

   'Example Serialize("qry1","field1",[field1])


   Dim rst As DAO.Recordset

   Set rst = CurrentDb.OpenRecordset(qryname, dbOpenDynaset, dbReadOnly)


   rst.FindFirst Application.BuildCriteria(KeyName, rst.Fields(KeyName).type, keyValue)


   Serialize = Nz(rst.AbsolutePosition, -1) + 1



   Set rst = Nothing



   Exit Function



   MsgBox "Error " & Err.Number & " in Serialize procedure: " & Err.Description

   GoTo Exit_Handler


End Function


Code Samples for Businesses, Schools & Personal Use

Updated 09/06/2018              


Its easy to create a rank order in Access reports but not so easy in queries.

If you Google the topic you will find several methods of doing so

The standard solution involves the uses of subqueries.

For example, see



Rank Order In Queries

For example in this query, the function is used to create a TableID field which serves as the row number:


SELECT Serialize("qryJSONFileTables","TableName",[TableName]) AS [TableID], MSysObjects.Name AS TableName, qryJSONFileTableNames.FileID, qryJSONFileTableNames.FileNameFROM qryJSONFileTableNames INNER JOIN MSysObjects ON qryJSONFileTableNames.TableName = MSysObjects.Name

WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Flags)=0))

ORDER BY MSysObjects.Name;


Attached is a simple example database using student assessment marks for a fictitious school to further illustrate its use


It has 2 tables - tblAssessmentMarks / tblAssessmentTypes and various queries including three with rank order fields



a) qryY7ScAss1MarkRANK

This sorts Y7 Science students in rank order by mark (descending order) - as several students have the same mark, the rank values are repeated e.g. two are ranked 2 so the next is ranked 4

QueryRANK.Example3 QueryRANK.Example2 QueryTiedRANK.Example

b) qryY7ScAss1PupilRANK

This is the same data but this time ranked in descending mark order by PupilID - so no repeated values for rank

c) qryY7HiAvgAssMarksRANK

This shows Y7 History average marks by assessment in rank order


Click to download    (Approx 0.85MB zipped)

Return to Code Samples Page