Select Based On Calculated Date 'Window'

[bernie321]

Hi

We have windows of time (calculated from the date records were created) when actions need to performed on certain records. So we need a report that shows records that are within this window when the report is run.

The Created date is named DateCreated.

The window is a 2 month period (one month either side) of every six months since Date Created.

E.G.
Date Created: 01/01/2006
1st Window: 01/05/2006 - 01/07/2006
1st Window: 01/12/2006 - 01/02/2007
........ and so on
(to be exact it is 2 months and one day but I can live with the 1 day less )

I am a bit stumpted on how to proceed.

Any advice would be much appreciated to lead me towards a solution.

Thanks
B

 

[muffntuf]

Could you do dateadd("d", +60,{datefield}) and likewise
dateadd("d", -60, {datefield}) for your range? This would put you 60 days on either side of your target date.


muffntuf

 

[lbass]

To pull records for the first three windows, you could use a record selection formula like:

currentdate in dateadd("d",-60, {table.date}+180) to
dateadd("d",60,{table.date}+180) or //1st window
currentdate in dateadd("d",-60, {table.date}+180*2) to
dateadd("d",60,{table.date}+180*2) or //2nd window
currentdate in dateadd("d",-60, {table.date}+180*3) to
dateadd("d",60,{table.date}+180*3) //3rd window

-LB

 

[bernie321]

Hi

Thank you both for your posts.

The only other thing that I forgot to mention is that the files can be open for 10+ years, its probably best if I estimate the longest a file will be open and add another 5 years to be sure.

Regards
B