Only show records older than 6 months

[Clairr]

Is there an expression to show records in a query that are older than 6 months?

At the moment I have to go into the database every month and change the criteria date ie:

<#01/09/2007#

It would be much easier if it could do this automatically with an expression.

Can anyone tell me if ones exists?

Thanks

 

[KenHigg]

You could do a datediff() for a given number of days, would that work?

 

[twoplustwo]

Ya DateDiff sounds good using like 150 days or something (I'm not sure if you can enter months?)

 

[Mike375]

<=(Date()-180)

 

[Clairr]

Thanks

Thanks very much, that automates it for me at least.

Is there definitely no way to make it use months instead of days?

If not I will have to filter out the results I don't want.

 

[twoplustwo]

Settings

The interval argument has these settings:

Setting Description
yyyy Year
q Quarter
m Month
y Day of year
d Day
w Weekday
ww Week
h Hour
n Minute
s Second

 

[KenHigg]

I'm sure you could write a function to do it but doing it in-line would be next to impossible I would think

 

[Clairr]

Solved

Thanks for introducing the DateDiff expression. I finally worked it out.

I created a new column with this in the field row:

Expr1: DateDiff("m",[event_date],Now())

Then in criteria set >6

That shows everything that has a month difference greater than 6 between todays date and the date in the Event_Date field!

 

[Sydcomebak]

Re: Solved

Thanks for introducing the DateDiff expression. I finally worked it out.

I created a new column with this in the field row:

Expr1: DateDiff("m",[event_date],Now())

Then in criteria set >6

That shows everything that has a month difference greater than 6 between todays date and the date in the Event_Date field!

Needed a similar filter myself. Thanks!
-Dave

 

[MSAccessRookie]

Did you look into using the DataAdd Function as a part of the Criteria? I have provided an example, and you can check out the Link Below for additional information.

[FONT=Courier New]< DateAdd("m",    -6, YourDate)[/FONT]
[FONT=Courier New]       [COLOR=red]I[B]nterval[/B][/COLOR] [COLOR=red][B]Count  Date[/B][/COLOR][/FONT]

http://www.techonthenet.com/access/functions/date/dateadd.php