Dale James
asked on
Change Natural Month Numbering
Hello Team...
I have a query which is filtered at the Contract Date field with the criteria:
Between DateSerial(Year(Date()),Mo
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
I have a query which is filtered at the Contract Date field with the criteria:
Between DateSerial(Year(Date()),Mo
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
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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Thank you to all for the great help and assistance received.
You are welcome.
Happy New Year!
Happy New Year!
Likewise, you are welcome!
Open in new window