12 Month Rolling Result

[A@Ron]

Hello.

I am using a query to sort data entries and label them with their corresponding year/month. The data is then going to Excel for analysis. Each year/month entry looks like "yyyy/mm".

The access field is:
Year/Mnth: Format([Labor_MBI.WO actual finish],"yyyy/mm")

My question: I want to set Criteria for Access to output the 12 most recently completed year/month only. Thus, in 2016/09, I want access to output 2015/09 through 2016/08. On 2016/10, it would now output 2015/10 - 2016/09. and so on... I have found a few different options but nothing that has worked for me.

I'm thinking my problem is that the "yyyy/mm" is not date formatted anymore so my criteria tries didn't work... Kinda guessing now. :banghead: Thanks for any / all help!

 

[A@Ron]

I should also note that in my data table, the format of [WO actual finish] is text. It looks like: 3/1/2016 2:30:00 PM.

 

[Minty]

I doubt very much your [WO Actual Finish] is really text. If it is you will have to convert it to a date to do the following.

Assuming you want whole months of results, Add [WO Actual Finish] to your query again and in the criteria put

>= DateSerial(Year(Date()),Month(Date())-12,1) AND < DateSerial(Year(Date()), Month(Date()+1), 1)

 

[A@Ron]

I doubt very much your [WO Actual Finish] is really text. If it is you will have to convert it to a date to do the following.

Assuming you want whole months of results, Add [WO Actual Finish] to your query again and in the criteria put

>= DateSerial(Year(Date()),Month(Date())-12,1) AND < DateSerial(Year(Date()), Month(Date()+1), 1)

Thanks! This mostly worked but I get a little bit of a strange result.

The data in my database includes Jan/2016 - Aug/2016. When I input like this:

>=DateSerial(Year(Date()),Month(Date())-7,1) And <DateSerial(Year(Date()),Month(Date()+1),1),

I get 7 months (Jan 2016 - July 2016). Perfect!

But, when I change the "7" to the following numbers, it starts to get crazy:

8: Feb - Jul (6 months)
9: Feb - Jul (6 months)
10: Feb - Jul (6 months)
11: no data!
12: no data!

Later, I will put more data in my table and see if it works. But, why is it doing this?

 

[Minty]

Actually it is because the year has changed - on further thought it might be better to subtract the year not the month, or use a DateAdd around the first of the month process;

Beginning of Current month = DateSerial(Year(Date()),Month(Date()),1)
So 12 months ago = DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1)))

 

[A@Ron]

Actually it is because the year has changed - on further thought it might be better to subtract the year not the month, or use a DateAdd around the first of the month process;

Beginning of Current month = DateSerial(Year(Date()),Month(Date()),1)
So 12 months ago = DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1)))

I loaded data in the table from 2015/08 - 2016/08.

I am hoping to get the results of 2015/08 - 2016/07.

When I enter:

>= DateSerial(Year(Date()),Month(Date())-12,1) AND < DateSerial(Year(Date()), Month(Date()+1), 1

I get: No Data



When I enter:

>=DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1))

I get: 2015/08, 2015/09, & 2016/08



When I enter:

>=DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1)) And <DateSerial(Year(Date()),Month(Date()+1),1)

I get: No data.



Sorry such a noob. Other ideas?

 

[Minty]

If you look at the table design - what data type is [WO Actual Finish] showing as?

 

[A@Ron]

If you look at the table design - what data type is [WO Actual Finish] showing as?

Text.

Should change to Date/Time?

 

[Minty]

Edit - Yes - dates should always be Stored as dates - otherwiese strange things happen - as per your response.

If you open the VBA code window and press ctrl G it opens the immediate window -
type ? DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1))
and you will see what it evaluates to (on my system 1/08/2015)
Do the same for ? DateSerial(Year(Date()),Month(Date())+1,1) - I get 01/09/2016

 

[A@Ron]

Edit - Yes - dates should always be Stored as dates - otherwiese strange things happen - as per your response.

If you open the VBA code window and press ctrl G it opens the immediate window -
type ? DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1))
and you will see what it evaluates to (on my system 1/08/2015)
Do the same for ? DateSerial(Year(Date()),Month(Date())+1,1) - I get 01/09/2016

Thank you. I originally tried to import the data as date/time but got conversion errors so I put everything in as text format. I was importing .csv files before. Today, I created new data tables and imported data as .xlsx files. I got no conversion errors while importing as date/time.

In the immediate window, my results were:

? DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1))
8/1/2015
? DateSerial(Year(Date()),Month(Date())+1,1)
9/1/2016
(We use US date format here)

The criteria that seems to work for me is your original proposal:
>=DateSerial(Year(Date()),Month(Date())-12,1) And <DateSerial(Year(Date()),Month(Date()+1),1)

For the other proposals:

When I input:
>=DateAdd("m",-12,DateSerial(Year(Date()),Month(Date()),1)) And <DateSerial(Year(Date()),Month(Date())+1,1)

I get:
All data?

Thanks for your help!