Trying to find on a calculated range of dates... There is a date field - and I want to set it to a year prior to first of current month. The calculation I'm trying to use is (Insert Calculated Result [select, "Item Date","calc"])

calc:

TextToDate(Month(Today)&"/01/"&(Year(Today)-1))&"..."&TextToDate((Month(Today)-1)&"/01/"&Year(Today))

I've also tried:

TextToDate(Month(Today)&"/01/"&(Year(Today)-1)&"..."&(Month(Today)-1)&"/01/"&Year(Today))

Trying these sytax - the first returns

731306...731641

The second doesn't return anything. Perhaps I need to calulate the start and end dates into globals - then build the range in the "insert calculated result" using the two globals and the "..."???

Any suggestions VERY welcome!

FileMaker Version: 6

Platform: Mac OS 9

Use Status(CurrentDate) instead of Today.

Dates are best specified using the Date () function. It's syntax is Date (month, day, year). The TextToDate function depends greatly on the date format specified by the Operating System which immediately makes it system and location dependent.

Date ( Month(Status(CurrentDate)), 1, Year(Status(CurrentDate) - 1) ) etc.

Figured it out. Calculation (in one piece) was too complex... so I figured out to "build" the contents of the field by using (Select) in first Insert Calculated Result ---

(Date ( Month(Status(CurrentDate)), 1, Year(Status(CurrentDate))-1)

(to set the start date)

Then NOT "select" in the next two:

... (to add the "range" operator)

Date ( Month(Status(CurrentDate))-1, 31, Year(Status(CurrentDate)))

(to add the ending date)

(Actually - I use a series of IF statements to handle year roll-overs and number of days in month (28,29,30 & 31). It finally dawned on me that's what the select / not(select) was for... building some pretty complex finds, etc.

Thanks.!

No need to worry about year rollovers or anything, FMP will sort out the dates itself.

The whole thing is not "too complex" to put into one calculation...

Actually, if you're putting it into a Find it should be different...

DateToText( Date ( Month(Status(CurrentDate)), 1, Year(Status(CurrentDate) - 1) ) ) & "..." &

DateToText( Date ( Month(Status(CurrentDate))-1, 31, Year(Status(CurrentDate))))

No need for any fancy calculations to determine month rollovers, just remember that the last day of any month is the day before the first day of the following month. So that last bit could be

DateToText( Date ( Month(Status(CurrentDate)), 1, Year(Status(CurrentDate))) - 1 )

DateToText( Date ( Month(Status(CurrentDate)), 1, Year(Status(CurrentDate))) - 1 )

also equals

DateToText( Date ( Month(Status(CurrentDate)), 0, Year(Status(CurrentDate))))

The zero day of this month is the last day of the previous one.

That's GREAT! Thanks. I figured someone had beat their head against the wall on such already... thanks for sharing your knowledge! F/M written documentation is - ah- not real verbose; often referring to the help files - which aren't exactly friendly nor much better. What supplemental references are recommended???