Jump to content

Insert calc problem - calculation too complex?


comcents
 Share

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

Link to comment
Share on other sites

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.

Link to comment
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)))

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

Link to comment
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 )

Link to comment
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.

Link to comment
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???

Link to comment
Share on other sites

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

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.