I have a query which is filtered at the Contract Date field with the criteria:
Between DateSerial(Year(Date()),Month(Date())-10,1) And DateSerial(Year(Date()),Month(Date())+2,0)
Basically this criteria is to only show all records which are up to 10 months behind the current month and to those which are up to one month head of the current month.
This criteria works perfectly.
With each record that is displayed, I have another column which uses the Month() function to display the natural month number of each record.
What I actually require is, because the query is basically a historical rolling yearly data query, I need is to display a different number against the record which is different to it's natural monthly numbering.
For example, naturally, January = 1, February = 2 etc but with this query, I require the numbers of each month to be numbered in accordance to the aforementioned criteria, e.g, oldest month being pulled with always have a monthly numbering of 1 and the month which is current month + 1 will always display 12. All other months will display their numbering in accordance to this require sequencing
So, currently, the oldest month being pulled is February which means this has a month number of 1, March = 2, April = 3 etc with January 2020 record dates having a month number of 12. Once we have January as the current month date and February 2020 becomes the + 1 month, February 2020 will then have a month number of 12 , January 2020 = 11, Dec 2019 = 10 and so forth.
As always, any suggestions would be greatly appreciated.