Scripting date range find

[AudioFreak]

Hello,

I would like to script a find to find all records for a month. I understand that searching for 1/1/2003...1/31/2003 will find all records for January 2003. But is there an elegant way of doing this? I only need to build reports for each month from last year and this year. I could do this with 24 scripts but there has to be a better way. Any suggestions or ideas would begreatly appreciated.

P.S. Happy New Year Everyone!!!

Michael

[-Queue-]

Why don't you use a calculation of Month( Date ) & "_" & NumToText( Year( Date ) )? You can then sort and summarize by it.

[AudioFreak]

Queue,

I should have known you would have a calc that would make this easier . How do you deal with capturing the start and end date for the report?

Thanks, Michael

[Vaughan]

The end of the month is the day before the first of the next month. So, you could have the user enter the month and year into two global fields (gMonth and gYear) and use them in a calculation

DateToText(Date(gMonth,1, gYear) & "..." & DateToText(Date(gMonth + 1, 1, gYear) - 1)

Here the user enters the *number* of the month (1 to 12). A nicer interface can be made whereby they select the month name, however this will require another calculation to conver the monthname into the month number.

The script will need to change to a layout with the date field on it, and use the Insert Calculated Result step to enter the date search string calculation.

[-Queue-]

Do you mean for the first and last day of each month?

You can use a text calculation: DateToText( Date( Month( date ), 1, Year( date ) ) ) & "..." & DateToText( Date( Month( date ) + 1, 1, Year( date ) ) - 1 ) and put this on the header or subsummary part.

[Vaughan]

Cool, Queue... the same calculation used for two different purposes!

[-Queue-]

I guess that means we know we got it right. Kind of like 'checking your work' in high school.

[Biomass]

Hi Vaughan,

I am seeking a way to find all the months represented between & including the Start Date and End Date. For example, if a project starts in January and is to finish in March, what calculation would I need to write so a find picks up the project to appear in discreet reports for January, February and March. Can't work out how to get it to recognise February. Hope this makes sense. Thanks for any suggestions.

Cathy

[AudioFreak]

Thanks Queue and Vaughn! Your suggestions sent me in the right direction.

[-Queue-]

Have you tried Insert Calculated Result ["date field", "DateToText( gStartDate ) & "..." & DateToText( gEndDate )"]?

The bold quotes are ones that FileMaker will automatically enter, so you won't need to. I show it this way because this is how it will look in ScriptMaker.

[Vaughan]

"what calculation would I need to write so a find picks up the project to appear in discreet reports for January, February and March"

Have you tried a summary report, using a "month" field as the break?