Searching for old Records by Month

[chevell]

I have a script that runs on the first of every month. What I want the script to do is to find the records that are older than three months. Because not all months end on the 31st, I have no idea how to do the query. I think it should be roughly <= Status (Current date) - 3 months and 1 day. Any ideas on this one? Thanks!

[QuinTech]

Hi, Chevell. Make a calc field, number, equal to:

Month ( theDateField ) <= Month ( Status ( CurrentDate ) )

Find for the number 1 in there, and you should be good to go.

HTH,

Jerry

[-Queue-]

Hmm. Jerry's calc will give you all records less than or equal to the current month for all years. This wouldn't work too well in January unless you only wanted all January records in your file. I think what you're looking for is

Enter Find Mode [ ]

Insert Calculated Result [yourdatefield; "<=" & DateToText(Date( Month(Status(CurrentDate)) - 3, Day(Status(CurrentDate)) - 1, Year(Status(CurrentDate)) ))]

Perform Find [ ]

This will be faster than a calc that would have to be unstored in order to update daily. Note that yourdatefield must be on the current layout when the script runs.

[chevell]

I *think* i get you but can you explain that code? The first part of the calculation finds the month name of the create date. The second part finds the month name of the current month. But what's the less than or equals do? And how do I find three months back? Thanks again!

[chevell]

hmm, ok, that makes more sense. I am going to try this out. If I am using the Current Date status function, will I need to close the database to update the value of that or will it update on its own? Thanks much!

[-Queue-]

It will update as soon as the script is called. It is only the Today function that requires closing and opening the file to update.

[QuinTech]