Filter and current date

[rob.lyles]

Hi, is there a way to set a filter to view records that have fields greater or less than the current date? For instance, in my table I have a field title Ship Date. I only want to view records that have ship dates older than 1 month. I.E. since today is 12/08/09, I would like to view the records with ship dates 11/08/09 and older.

Any help is appreciated. Thanks.

[OllyInMunich]

Hello Rob,

A filter expression called Today()-[Ship Date]>30 would be a crude approach as some months have 28 or 31 days.

Month(Today())-Month([Ship Date]), also Day(), Year() expressions, would help you calculate a more precise filter.

Best wishes,

Olly

[Grant Perkins]

Hi Rob,

Sorry to hijack your thread but I notice that you have V8. Things have moved on since then and more options have opened up for users of later versions so I thought it would be worth mentioning a few more options.

The Age() and DateAdjust() function might be another way to make this calculation in ways that take into account the number of days in a month. There are also now 'standard' functions (of the user defined type) to find. inter alia, the end date of a month or the start date of a month and thus, for example, quickly ascertain how many days there were in last month and use the answer to set the cut off date, perhaps with a user specified tolerance factor (e.g 1 month plus 3 days) for selections based on ageing ...

You can do most of the same things in V8 (and earlier versions) but you need to create the formulas for each stage if there is no suitable function to do it for you.

HTH.


Grant

[Data Kruncher]

There's nothing like building filter formulas in the morning to start the day off right... :D


ShipDate <= If(Month(Date())=1,
CtoD("12/"+Trim(Str(Day(Date())))+"/"+Trim(Str(Year(Date())-1)),"m/d/y"),
CtoD(Trim(Str(Month(Date())-1))+"/"+Trim(Str(Day(Date())))+"/"+Trim(Str(Year(Date()))),"m/d/y"))

Age() is much more elegant, isn't it?

Kruncher