Newbies pjmo Posted November 7, 2002 Newbies Share Posted November 7, 2002 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 Link to comment Share on other sites More sharing options...
Fitch Posted November 7, 2002 Share Posted November 7, 2002 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! Link to comment Share on other sites More sharing options...
Newbies pjmo Posted November 15, 2002 Author Newbies Share Posted November 15, 2002 Tom, Thank you (belated) - I'm working on it Thanks for the quick reply Peter Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 7967 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 accountSign in
Already have an account? Sign in here.
Sign In Now