Rolling year...

[Fiona H]

Hi all,

I wonder if anyone could give me a starting point to set up a rolling year query? My companies month runs from the 15th to the 14th. On the 16th of each month, they want to run a report showing how many hours of overtime have been worked in the last 12 months.

So on the 16th September I need to compare the following time periods:

15th August - 14th September
15th July - 14th August
15th June - 14th July
15th May - 14th June
15th April - 14th May
15th March - 14th April
15th February - 14th March
15th January - 14th February
15th December - 14th January
15th November - 14th December
15th October - 14th November
15th September - 14th October

I would really appreciate a starting point! Thanks

 

[John Big Booty]

Have a look at the available date functions, you should be able to use the DateAdd() function combined with the end of the current reporting period to produce the criteria for the start of the 12 month reporting period.

 

[Fiona H]

Ack... I am looking at date functions and playing with DateAdd without any luck.

As I don't have a full 12 months worth of rolling data yet, I tried to start small and bring back the last months worth of data with this
=DateAdd("m",-1,Now())

But even that isn't working for me

 

[John Big Booty]

Unless you specifically need a time component in your date use Date() in preference to Now().

 

[Fiona H]

whoop whoop... another lap of honour round the office!!

It now works using Date()

Thank you very much.

Now I just need to figure out how to make it go into 15th-14th segments

 

[John Big Booty]

Try;

DateAdd("m",-12,(DateSerial(Year(Date()),Mounth(Date()),15))

to return the start date for your report period, and;

DateSerial(Year(Date()),Mounth(Date()),14)

to return your end date.

 

[Fiona H]

You're amazing thank you so much for sorting the start and finish dates of this report for me!

I feel very cheeky asking after all the help you have given me already, but do you know how to group them into 15th-14th sections for each month period? I'm having no luck with that at all!!!

 

[John Big Booty]

You could either hard code the 12 start and finish dates based on the code I've already given you. Or more elegantly use a do loop to create the 12 sets of start finish dates (once again based on what I've already demonstrated) either incrementing up or down by one each time until you get the 12 sets of data you are after.

 

[Fiona H]

I love the idea of being able to do something elegant with access... maybe one day

Thank you again for all your help today!