query syntax for show records older than 1 year

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)

Select allOpen in new window

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?

I don't understand what you are trying to do beyond your 'one year prior to yesterday' description.

In your last question, I assume you are talking about accommodating the leap year?

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

Dale yes that's what I'm talking about

@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"

You must use DateAdd if you wish to include Feb. 29th:

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

/gustav

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

Great followup and comments to make sure I was on the right track with what I was looking for.

Works great.