Date Calculation Programming Challenge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rose - 30 Sep 2006 05:38 GMT

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.

Signature

Rose

Reply to this Message

Sreedhar - 30 Sep 2006 13:01 GMT

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)

Signature

Sreedhar

> 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
[quoted text clipped - 7 lines]
> field), how do I calculate the number of days in March. This is time
> sensitive any assistance anyone can give is greatly appreciated.

Reply to this Message

Duane Hookom - 30 Sep 2006 14:46 GMT

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.

Signature

Duane Hookom
MS Access MVP

>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
[quoted text clipped - 10 lines]
> field), how do I calculate the number of days in March. This is time
> sensitive any assistance anyone can give is greatly appreciated.

Reply to this Message