Access Query Filter Between Date Range

I am running a query on a single table.   This table includes a date for every record.  I would like to filter the data by the date where the data would include the last 12 months but starting from the previous month from the current date.  So in other words, it would not include any data from the date for the current month when the query is run.  What is the best way to write a criteria for this in the date column?
shrimpforkAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
use this criteria

between dateserial(year(date()),month(date())-1,day(date())) and dateserial(year(date())-1,month(date())-1,day(date()))
0
shrimpforkAuthor Commented:
I need it to include all the data from the previous month...so the data if run today would be from April 30, 2015...back to April 1, 2014.
0
Rey Obrero (Capricorn1)Commented:
between dateserial(year(date()),month(date()),0) and dateserial(year(date())-1,month(date())-1,1)
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PatHartmanCommented:
Between dateadd("m", -1, dateserial(Year(date()), month(date()), 1)) and dateserial(year(date()),month(date()),0)
The first expression subtracts 1 month from the first day of this month.  It is necessary to use dateadd() to handle year end correctly. The second expression is a bit of a trick.  It uses the current month but 0 as the day.  Since 0 is invalid as a day, Access backs up 1 day which conveniently gives use the last day of the previous month.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
shrimpforkAuthor Commented:
@ PatHartman:  I'm trying to understand your comment with the dateadd function added.  When you stated "...to handle year end correctly...", I'm not sure what you mean about this.  I'm not sure if you are talking about the calendar year or the end of the between date.  Can you explain?  It seams that the previous suggestion from Rey Obrero is working without using dateadd.
0
Rey Obrero (Capricorn1)Commented:
you don't need the DateAdd
0
PatHartmanCommented:
You are correct.  Access works out that  1 - 1 is not a valid month and so converts it to 12 for the previous year.  
So:
Between dateserial(Year(Date()), month(Date()) -1, 1) AND dateserial(year(date()),month(date()),0) also works.

Rey's suggestion has an extra -1.  In addition to subtracting 1 from the month, it also subtracts 1 from the year.  It also goes high date to low so  it would be
Between 4/30/15 AND 4/1/14
rather than
Between 4/1/15 and 4/30/15
The high to low range check probably works but we don't usually write the range check that way.  But the start date becomes April 1 of last year with the extra -1.
0
shrimpforkAuthor Commented:
Both answers work.  I prefer the correct date between range from low to high.  Thanks guys for the comments and clarification.
0
Rey Obrero (Capricorn1)Commented:
< I prefer the correct date between range from low to high>

it doesn't matter which come first, the Between function is inclusive.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.