Trying to Find Records more than 12 months old?

[Todd]

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.

[Fitch]

Go to layout (with date field on it)

Enter Find mode ()

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

Perform Find ()

[Todd]

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.

[kenneth2k1]

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

[Todd]

I've tried it both way and neither works.

Todd

[Ugo DI LUCA]

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.

[kenneth2k1]

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

[Ugo DI LUCA]

Ooops

[Todd]

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

[-Queue-]

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

[Todd]

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

[-Queue-]

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.

[-Queue-]

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

[Todd]

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

Todd

[kenneth2k1]

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

[Fitch]

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.

[Fitch]

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) ) )

[-Queue-]

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

[kenneth2k1]

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