Change Natural Month Numbering

Dale James
Dale James used Ask the Experts™
on
Hello Team...

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.

Sincerely

Dale
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
You need to use the DateAdd function.
Between DateAdd("m", -10, Date()) And DateAdd("m", 1, Date())

Open in new window

John TsioumprisSoftware & Systems Engineer

Commented:
If you plan to have frequent month renumbering maybe it's better to have the months in a table and just assign the numbers as you like...then just query the table and get the active mapping month <--> number.
The benefit is that today you want February to be 1st.. tomorrow it can be June... instead of fiddling in code to see what number to add/substract..you just make the changes in the table and you are good to go
Mark EdwardsChief Technology Officer
Commented:
Dale:  Sounds like you want to calculate the difference in months between the contract date and today, but with an offset.  Here's a query with a formula that should work for you.  You can change the offset number (in this example its +11) to whatever number works for you.
Query-to-Calculate-Months-Design.pngThe query produces the following result:
Query-to-Calculate-Months-Result.pngYou could simply filter for months 1 to 12.  Using your current filter, you should only see months 1-12.
Is this what you are looking for?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You can include this expression for a field in your query to calculate the month number  of the retrieved values for Contract Date:

MonthNo: (Month([Contract Date]) + 10) Mod 12 + 1

Open in new window

Dale JamesTherapist

Author

Commented:
Hello Team...

My apologies for the delay with my response due to the holiday period.

Thank you to all for your responses and suggestions. Each in their own way a solution and with this particular issue both Mark and Gustav's suggestions worked most appropriately.

Once again...I just want to thank all for the continued assistance received.

Sincerely

Dale
Dale JamesTherapist

Author

Commented:
Thank you to all for the great help and assistance received.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome.
Happy New Year!
Mark EdwardsChief Technology Officer

Commented:
Likewise, you are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial