Date Range Criteria (2 Viewers)

[pickslides]

I have the following code where I am trying to limit the query to pick up the last 12 or 13 months of data only.

TRANSFORM Round(Avg([Net Price]),0) AS [Avg Net Price]
SELECT [TIP Insurance Raw Data].[Agency Group Name]
FROM [TIP Insurance Raw Data]
WHERE ((([TIP Insurance Raw Data].[Agency Group Name])<>"Adventure World") AND (([TIP Insurance Raw Data].[Net Price])>=0) AND (([TIP Insurance Raw Data].[Issued Date]) [COLOR=red]Between Date() And Date()-365)[/COLOR])
GROUP BY [TIP Insurance Raw Data].[Agency Group Name]
PIVOT Year([Issued Date]) & "/" & Month([Issued Date]) In ("2010/1","2010/2","2010/3","2010/4","2010/5","2010/6","2010/7","2010/8","2010/9","2010/10","2010/11","2010/12","2011/1","2011/2","2011/3","2011/4","2011/5","2011/6","2011/7","2011/8","2011/9","2011/10","2011/11","2011/12","2012/1","2012/2","2012/3","2012/4","2012/5","2012/6","2012/7","2012/8","2012/9","2012/10","2012/11","2012/12","2013/1","2013/2","2013/3","2013/4","2013/5","2013/6","2013/7","2013/8","2013/9","2013/10","2013/11","2013/12","2014/1","2014/2","2014/3","2014/4","2014/5","2014/6","2014/7","2014/8","2014/9","2014/10","2014/11","2014/12","2015/1","2015/2","2015/3","2015/4","2015/5","2015/6","2015/7","2015/8","2015/9","2015/10","2015/11","2015/12");

This code works but I want to get the whole month, this is giving me the days either side.

Hope this makes some sense.

MQ

 

[namliam]

Try something like:
Between dateadd("M", -12, (date - day(date))) and date

 

[Brianwarnock]

I would use DateSerial

Between Dateserial(year(Date),month(Date)-13,1) And Dateserial(year(Date),Month(date),0)

The first parameter takes you back to the first of the month 13 months ago and the second to the last day of last month.

BTW spaces in object names are a pain, it forces you to use [], lead to syntax errors and stops Access helping with Intellisense

Brian