Incremental counter for dates - Access 97

[robertlevine]

Ok, here's my dilemma - What I'm trying to do is perform an incremental counter on a table, based on the dates - by month.

For example, here is the date data in the table... and my desired "counter" result:

date desired result
------ -------------
1/3/05 1
1/4/05 2
1/5/05 3
1/7/05 4
1/8/05 5
2/1/05 1
2/2/05 2


So, as you can see, I'd want to be able to update the "counter" field with an incremental for each day of the month... and then start back at 1 on the first day of the 2nd month.

Thanks for helping! This has been stumping me for hours.

 

[pbaldy]

Here's one way in a query:

DateRank: DCount("*","TableName","DateField < #" & [DateField] & "# AND Month(DateField) = " & Month([DateField]) & " AND Year(DateField) = " & Year([DateField]))+1

 

[robertlevine]

Absolutely awesome! You're the greatest!

Absolutely awesome! You're the greatest! Now all I have to do is figure it out!