query syntax for show records older than 1 year

I am trying to get the right syntax for getting all records 1 year old from "yesterday"

Tried several things but can't seem to get it right...
Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?

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

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.

Dale FyeOwner, Developing Solutions LLCCommented:
Well, because, of the difference between years, you cannot simply use the DateDiff( ) based on either years or days (365).  Instead, you should create a new date based on yesterday, something like:

WHERE [DateField] < DateSerial(Year(Date()-1), Month(Date()), Day(Date()-1))

This will will create a new date 2014/10/22 and compare your date field to that value.  The thing you need to consider is what date is a from yesterday if you are in a leap year.  If today is March 1st of a leap year, then is a year ago yesterday March 1, or Feb 28?
This is another way to point to the date you seek:
WHERE [DateField] < DateAdd("yyyy", -1, Date()-1)

Open in new window

Ernest GroggSecurity Management InfoSecAuthor Commented:
Great thought.  So is there something that accommodates for that? Say on the box (workstation)?  The date is driven from the box but the 1 year and a day isn't...is there anyway for us to accommodate?
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.

I don't understand what you are trying to do beyond your 'one year prior to yesterday' description.
Dale FyeOwner, Developing Solutions LLCCommented:
In your last question, I assume you are talking about accommodating the leap year?
Rey Obrero (Capricorn1)Commented:
if you are looking for records that are a Year old from yesterday, try

select * from yourtable
where [datefieldname] = dateserial(year(date()) -1,month(date()), Day(Date())-1)

change "yourtable" and "datefieldname" with actual names of table and field
Ernest GroggSecurity Management InfoSecAuthor Commented:
Dale yes that's what I'm talking about
Rey Obrero (Capricorn1)Commented:
@Ernest Grogg,

better if you post sample records and specify which records should be returned by your query.

a record with date 10/22/2014  is a year old based on the date yesterday which is 10/23/2015

the title of your question contradicts the statement you posted above

         -  query syntax for show records older than 1 year

          -- I am trying to get the right syntax for getting all records 1 year old from "yesterday"
Gustav BrockCIOCommented:
You must use DateAdd if you wish to include Feb. 29th:

    Where DateAdd("yyyy", 1, [DateField]) < DateAdd("d", -1, Date())

Ernest GroggSecurity Management InfoSecAuthor Commented:
So the Record looks like:

PId: 1
FID: 1
BackDate: 10/22/15
BackDateCheck: 07/22/16
BackDateActual: 10/22/15

If the BackDateActual is 10/22/15  then I need the query to check for all records that have a date of 10/21/14  ( was using from 10/21/15 to go back 1 year 10/21/14) to move those into an Archived state.  So basically they are looking for all employees that need new backgrounds (they expired "yesterday")

So along with the query pulling the records, I am going to mark them as archived to required a Background again.  A secondary query would then run to give a report to the User (HR) that the following employees would require new backgrounds, which would be the ones  marked as archived.

Sorry got sidetracked....
Rey Obrero (Capricorn1)Commented:
try this query then,

select * from yourtable
 where [BackDate] = dateserial(year([BackDateActual]) -1,month([BackDateActual]), Day([BackDateActual])-1)

change "yourtable" with actual name of table

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
Ernest GroggSecurity Management InfoSecAuthor Commented:
Great followup and comments to make sure I was on the right track with what I was looking for.

Works great.
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.