Monthly Date Range: 28, 29, 30 or 31?

[HazMatt]

30 days hath september...

I'm trying to figure out how to make my date range work. I want to find monthly reports. I've figured out the basic mechanism for getting a date range into a find script (using Insert Calculated Result, not Set Field), but I'm having trouble with the actual calculation.:

(Month(Status(CurrentDate)) - 1)

& "/" &

"1"

& "/" &

Year(Status(CurrentDate))

& "..." &

(Month(Status(CurrentDate)) - 1)

& "/" &

"31"

& "/" &

Year(Status(CurrentDate))

The problem is the month's ending date. Since they vary, I need to account for all possible months.

I also just realized that my script won't work in January. How can I make this work?

[LiveOak]

The easiest way to avoid having to figure out how many days in a month is to make the end date one day less than the first day of the next month:

Date(Month + 1, 1, Year) - 1

This backs up one day from the first day of the next month. This is just the concept, you will have to work out the actual formulas for your application.

-bd

[-Queue-]

Or Date(Month + 1, 0, Year).

[Jim McKee]

MD ...

Begin Date:

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

End Date:

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

In the End Date formula, what you're going to get is "the first day of the current month - 1", or, in other words, "the last day of the previous month." Follow that?

FileMaker is smart enough to handle the Month and Year bits of the calc when you have values like +1 or -1 or whatever in the Day argument. Filemaker also handles leap year issues within these date calcs.

Good luck!

[HazMatt]

Yes! Sometimes you have to think outside the box, I guess.

Thanks, I learned a small tidbit from all three replies.

[-Queue-]

Simplified again, make Day(1) 1 and Day(1) - 1 0.