Union Query (rolling 13 month)

[dsellers1]

So below is my union query that I am having issues with. I am trying to get it to pull the last 13 months (Sep 2010 through Sep 2011) but am having some issues. When I set it to ("m", -13, ... it pulls August 2010 through August 2011. When I change it to -12, the query pulls the same date range minus September 2010. I have searched many forums and tried their solutions but seems to end up with the same problems. Any thoughts or suggestions would be greatly appreciated.


SELECT [Month Reporting],[Portfolio],[Call Volume],[Abandonment Rate],[Average Calls Per Day]
From [qryPhonesTotalsUS]
WHERE Format([Month Reporting], "mmmm yyyy")<=Format(DateAdd("m", -13, Date()), "mmmm yyyy")

UNION ALL SELECT [Month Reporting],[Portfolio],[Call Volume],[Abandonment Rate],[Average Calls Per Day]
From [qryPhonesTotalsPR]
WHERE Format([Month Reporting], "mmmm yyyy")<=Format(DateAdd("m", -13, Date()), "mmmm yyyy")
ORDER BY Portfolio, [Month Reporting];

 

[vbaInet]

When you say the last 13 months, considering we're in October 2011, don't you mean between September 2010 and October 2011?

 

[dsellers1]

I actually need Sep through Sep. I do these reports at the beginning of each month and they focus on last months data. This is the only query that I need that shows a 13 month span

 

[plog]

Your WHERE clause is all kinds of messed up. When you run it through the Format function it makes it compare strings, not dates. That means if you run it in October the only month not showing is September (because when comparing strings "S">"O"). Next month your going to lose October as well (because "O">"N").

What data type is [Month Reporting] exactly? Can you provide some sample data in it?

Also, even if your WHERE clause wasn't comparing strings you have it set up to pull in all data prior to 13 months, not data 13 months and newer as you have stated you want. To correct this you would need to change the <= in your WHERE clause to >=.

And don't get me started about why this shouldn't probably be a UNION query.

 

[dsellers1]

Makes since. So I simplified the WHERE clause and actually set my <= to a BETWEEN instead of >=. It worked!

Where [Month Reporting] Between DateAdd("m", -14, Date()) And DateAdd("m", -1, Date())

This shouldn't cause issues down the road...I don't think