What Access formula is usee to retrieve data from 6 mo. ago?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am having difficulty with determining what formula to use to retrieve
records from 6 months ago in my Access database. I know this should be
incredibly simple, but nothing I've tried has worked. I have a field named
"Date of Hire" that I want to return the records for everyone hired in the
month equaling 6 month previously.

Can anyone help?
 
Make a column in your query like -- Hire Month: Month([Date of Hire])
Make a column in your query like -- Hire Year: Year([Date of Hire])

Add the following criteria for the columns --
Month(DateAdd("m",-6,Date())) Year(DateAdd("m",-6,Date()))
 
So, if it is July 15th, you want anyone Every hire between January 1
and january 31st?

OK.

IIf(Month(Now())-6<1,Year(Now())-1,Year(Now())
This gives you the year 6 months ago.

IIf(Month(Now())-6<1,Month(Now())+6,Month(Now())-6)
This gives you the month 6 months ago.

So If

Month([DateofHire])=IIf(Month(Now())-3<1,Month(Now())+6,Month(Now())-3)
and

Year([DateofHire])=IIf(Month(Now())-6<1,Year(Now())-1,Year(Now()))

Then Date of hire is somewhere in the month of whatever is the month 6
months from today.

Clear?

Phil
 
Use criteria like the following.

Between DateSerial(Year(Date()),Month(Date())-6,1) and
DateSerial(Year(Date()),Month(Date())-5,0)
 
So, if it is July 15th, you want anyone Every hire between January 1
and january 31st?

OK.

IIf(Month(Now())-6<1,Year(Now())-1,Year(Now())
This gives you the year 6 months ago.

IIf(Month(Now())-6<1,Month(Now())+6,Month(Now())-6)
This gives you the month 6 months ago.

So If

Month([DateofHire])=IIf(Month(Now())-3<1,Month(Now())+6,Month(Now())-3)
and

Year([DateofHire])=IIf(Month(Now())-6<1,Year(Now())-1,Year(Now()))

Then Date of hire is somewhere in the month of whatever is the month 6
months from today.

Clear?

Throw that in a query field, and put -1 in the criteria for that field.
It will retrun what you are looking for.


Phil
 
Back
Top