Query to get value of number of occurrence

[TB11]

Is there a way to create an expression that will assign a sequential value based on the sorted date? So, if it is the first date, the value =1, and if it is the fifth date, the value =5. the twentieth date =20, and so on.

Query, sorted in ascendign order on date field:
IDProject, ProjectDate, WISHLIST-CALCULATED NUMBER ORDER OF OCCURRENCE
10, 3/1/2021,1
3, 4/5/2021, 2
5, 4/20/2021, 3
22, 7/8/2021, 4
52, 9/15/2021, 5


Thanks.

 

[theDBguy]

Will there be duplicate dates?

 

[TB11]

@theDBguy there should not be duplicate dates

 

[bob fitz]

You could use a calculated field. Perhaps something like:
RecCount: DCount("*","[B]YourTableName[/B]","ProjDate<=#" & Format([ProjDate],"yyyy/mm/dd") & "#")
but this may be a little slow if the recordset is large.

 

[TB11]

@bob fitz THANK YOU! Exactly what I needed.