# Insert calc problem - calculation too complex?

This topic is 6505 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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

##### Share on other sites

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.

##### Share on other sites

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

(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.!

##### Share on other sites

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 )

##### Share on other sites

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.

##### Share on other sites

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

##### Share on other sites

This topic is 6505 days old. Please don't post here. Open a new topic instead.

## Create an account

Register a new account