days in residence in month

[pjmo]

Hi, I have been trying to figure this out for a while. Using FM 5.5, Windows. I have created a homeless shelter resident tracking system.

Among the fields are "datein" and "dateout". I need to run reports monthly showing how many days the residents were here in that month. THere are residents that were there all month, some that came in during the month and left in that month and others that came in during the month and left either the next month or are still here. The date out may be empty if the resident is still here. So, for each resident that was here in the month, I need to determine how many days the resident was here.

i.e.,

J. Smith - 5

B. Smith - 21

G. Jones - 30

I want to set this up so I can do this every month and eventually do annual reports.

Sorry for the length of posting and thanks in advance for any help

Peter

[Fitch]

Let's say you have a number field, ReportMonth

Calculate the FirstDay of the ReportMonth:

Date( Month(ReportMonth), 1, Year(ReportYear))

Calculate the LastDay of the ReportMonth:

Date(Month(ReportMonth) + 1, 0, Year(ReportYear))

## That's a fun way of doing it that I credit to Djukic Goran.

The less evil way would be:

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

First you'll need to find everyone, you can script this:

Request1: datein =< LastDay / dateout => FirstDay

Request2: datein =< LastDay / dateout = "" (blank)

Hint: Set Field doesn't work very well when scripting date finds. Use Insert Calculated Result, but make sure the script goes to a layout that the datein and dateout fields are on.

Then you'll have to test for a number of possibilities with a Case() calculation -- calculate the End Date first, then subtract the Start Date:

Case(dateout < LastDay and not IsEmpty(dateout), dateout, LastDay) -

Case(datein < FirstDay, FirstDay, datein) + 1

The plus one is there because I assume if someone checks in and out the same day you count it as one day, not zero.

Don't worry about the length of your post, I thought it was clear and concise. I've seen a lot worse!

[pjmo]

Tom,

Thank you (belated) - I'm working on it

Thanks for the quick reply

Peter