I'm trying to write a script that will find all records in my database which are more than 12 months old. Not necessarily just this year -1, but more like today - 365. My date fields exist as mm/dd/yyyy (all in one field). What's the best way to go about this? Thanks.

Go to layout (with date field on it)

Enter Find mode ()

Insert Calculated Result(yourDate, "<" & TextToDate (Status (CurrentDate) - 365) )

Perform Find ()

I'm getting no joy when I try to enter that formula. I keep getting a "too many separators in that function" message and the highlight is on the last 2 digits in the year. What am I doing wrong? Thanks.

I don't see a specific date anywhere in Fitch's calc. Are you entering an actual date, or the date field?

I've tried it both way and neither works.

Todd

Tom's formula is

"<" & TextToDate (Status (CurrentDate) - 365)

make sure you didn't entered

"<" & TextToDate (Status (CurrentDate) - 365))

FM error capture is not that good to locate the bad string.

Ok, ok. I think I know...

Use DateToText instead of TextToDate

That won't fix your too many separators error, but you will need it.

Ken

Ooops

For the life of me, I can't get that formula to work- here's what I have so far - please let me know where I've gone wrong, since I've never used the Calculated Result step before:

Go To Layout (date field)

Find All

Go To Field (Date)

Enter Find Mode (Pause)

(User enters date)

Insert Calculated Result: "<" & DateToText(Status(CurrentDate) - 365)

Perform Find

All I keep getting is a "No Records Match This Request" error - but they are definitely present.

Help!

Thanks,

Todd

User enters date and then you overwrite their selection based on today's date?

I think I get it - I don't have them enter anything in the date box, right? Just use the calculation.

Actually, if you're not using the 'Select' option with your Insert Calculated Result, then you're find criteria is "entereddate < today - 365", which isn't a result; it's a conditional test! It doesn't appear that you even need a user-entered date. But if you do, not using Select forces the inserted calc to be appended to the field's current contents, instead of replacing them.

Furthermore, it would appear you are using a less than "<", yet you want to search for records greater than, ">", one year.

It works! Thanks. (works with the "<", not the ">")

Todd

Well it took like 4 of us to figure it out, but we finally got it!

Well, that was fun. Sorry about the "TextToDate" -- my fingers got ahead of my brain. As for "<" -- even though we may phrase it in English "greater than a year old," what that means in logical terms is a date "less than" the date a year ago.

PS: even though you specified "today - 365" in your original question, I think you'd get a more accurate result if you did use a "year - 1" construct:

Insert Calculated Result: "<" &

DateToText( Date (

Month(Status(CurrentDate) ),

Day(Status(CurrentDate) ),

Year(Status(CurrentDate) - 1) ) )

Yeah, nevermind me. It's been a long week. This proves that I should be sleeping instead of eagerly programming.

I think we should all hit the showers and try for another W tomorrow.