Date Calculation Programming Challenge

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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)
 
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.
 
Back
Top