find mode and date fields

[gregobad]

I want to write a script which finds all records older than two weeks. I tried entering "< // - 14" into the Create Date field, but it won't accept that as a valid date. "// - 14" is okay, and so is "< //" but it doesn't like it when you put them together. I have tried several ways to get around this, and so far none work. I am using FM 4.1 for Windows NT. Any suggestions?

[Louisa]

hi,

This may just be a simplistic response, but use brackets! Try it so that the comparison is date < ( // - 14 ). FMP seems to like brackets!

[gregobad]

I tried using brackets before, but it didn't make any difference. thanks anyway

[spragueg]

Use a calculation field "cResult" to perform the (Today-14) operation then paste in the result like this...

Enter Find Mode

Set field ["Date Field", " "<"& cResult" "]

Perform Find [ ]

Hope this helps

G

[gregobad]

thanks sprague, I found out you can condense those lines into one:

set field ["Create Date", ""< " & (Today - 14)].

the key is using quotes and the & symbol. However, when the script pastes that calculation into the field, it deletes the "<"- finding only those records which are exactly 2 weeks old, not more than 2 weeks old. I don't know why it does this and so far I can't get it to stop. Using "Paste Result" retains the less than symbol, but converts the date into an unformatted string of numbers. Do you have any idea what is going on?

[KentL]

You do need to use paste result for this. However, you'll need to add the DateToText function. That way, it will place all the info into the field, just as if you typed it in.

Paste Result["Create Date", ""< " & DateToText((Today - 14))].

The Same thing has to be done for date ranges.

Paste Result["Create Date", ""DateToText(Starting date) & "..." & DateToText(Ending Date)""