Date Calculation Programming Challenge Rose 30.09.2006 04:38:02 I am working with a Microsoft Access Database helping a non profit organization try to figure out how many nights in a month people are staying in there shelter. I have two fields a Check In Date and Check Out Date Field. Therefore to figure out the nights a person stayed, I minus the Check Out by the Check In Date. The problem is if they check in the prior month, let’s say February and checked out in March, How I can I calculate just the days in March. The other problem is if they checked in March and have not checked out yet (null field), how do I calculate the number of days in March. This is time sensitive any assistance anyone can give is greatly appreciated. -- Rose

RE: Date Calculation Programming Challenge Sreedhar 30.09.2006 12:01:02 Hi Rose, Assuming the stay will not be beyond two months (?) as I understand from your question, try this untested code: Dim DaysOccupied As Integer Dim OverTwoMonths As Boolean Dim LastDayOfCheckInMonth As Date Dim DaysInCheckInMonth As Integer Dim DaysInCheckOutMonth As Integer If IsNull(CheckOutDate) Then CheckOutDate = Date DaysOccupied = DateDiff("d", CheckInDate, CheckOutDate) If Month(CheckInDate) = Month(CheckOutDate) Then OverTwoMonths = False Else OverTwoMonths = True LastDayOfCheckInMonth = DateSerial(Year(CheckInDate), Month(CheckInDate) + 1, 1) - 1 End If DaysInCheckInMonth = DateDiff("d", CheckInDate, LastDayOfCheckInMonth) DaysInCheckOutMonth = DateDiff("d", LastDayOfCheckInMonth, CheckOutDate) -- Sreedhar "Rose" wrote: [Quoted Text] > I am working with a Microsoft Access Database helping a non profit > organization try to figure out how many nights in a month people are staying > in there shelter. I have two fields a Check In Date and Check Out Date > Field. Therefore to figure out the nights a person stayed, I minus the Check > Out by the Check In Date. > > The problem is if they check in the prior month, let’s say February and > checked out in March, How I can I calculate just the days in March. The > other problem is if they checked in March and have not checked out yet (null > field), how do I calculate the number of days in March. This is time > sensitive any assistance anyone can give is greatly appreciated. > > -- > Rose

Re: Date Calculation Programming Challenge "Duane Hookom" <DuaneAtNoSpanHookomDotNet> 30.09.2006 13:46:10 You have asked this question at least 3 times in 3 different news groups. I haven't seen you reply in any of your previous threads. Again, please limit your individual questions to a single news group and thread. New questions should begin new threads. -- Duane Hookom MS Access MVP "Rose" <Rose[ at ]discussions.microsoft.com> wrote in message news:787C4D20-FADD-4838-A693-5C073A05493E[ at ]microsoft.com... [Quoted Text] >I am working with a Microsoft Access Database helping a non profit > organization try to figure out how many nights in a month people are > staying > in there shelter. I have two fields a Check In Date and Check Out Date > Field. Therefore to figure out the nights a person stayed, I minus the > Check > Out by the Check In Date. > > The problem is if they check in the prior month, let's say February and > checked out in March, How I can I calculate just the days in March. The > other problem is if they checked in March and have not checked out yet > (null > field), how do I calculate the number of days in March. This is time > sensitive any assistance anyone can give is greatly appreciated. > > -- > Rose