Query to get value of number of occurrence (1 Viewer)

TB11

Member
Local time
Today, 13:50
Joined
Jul 7, 2020
Messages
78
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

I’m here to help
Staff member
Local time
Today, 11:50
Joined
Oct 29, 2018
Messages
21,358
Will there be duplicate dates?
 

TB11

Member
Local time
Today, 13:50
Joined
Jul 7, 2020
Messages
78
@theDBguy there should not be duplicate dates
 

bob fitz

AWF VIP
Local time
Today, 18:50
Joined
May 23, 2011
Messages
4,717
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.
 

Users who are viewing this thread

Top Bottom