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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Rey Obrero (Capricorn1)Commented:
between dateserial(year(date()),month(date()),0) and dateserial(year(date())-1,month(date())-1,1)
0
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.