Date Calculation Programming Challenge

[Guest]

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.

 

[Guest]

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)

 

[Duane Hookom]

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.