Macro to calculate days in a month

  • 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.
 
Rose,

This is not a job for a Macro. It is a calculation, which can be
performed either in a Query, or in the Control Source of a textbox on
your Form or Report. One way to get the last day of the month is via
the DateSerial function, for example...
DateSerial(Year([Check In Date]),Month([Check In Date])+1,0)-[Check In
Date]
.... will give the number of days in March if the Check In Date is in March.
 
This is the challenge. If I get the Total Nights to calculate based on
[CheckOut]-[CheckIn], how can I have it only calculate the days they stayed
in this case June. See Example below:

In number one they have not check out yet so I need it to calculate against
the last day of the month in June.

Number 2 and 3 calculate correctly

In Number 4 the Check In Date is in May but I only want it to count Jun 1 to
June 2.

Check In Check Out Total Nights
1. 6/1/2006
2. 6/3/2006 6/6/2006 3
3. 6/5/2006 6/10/2006 5
4. 5/25/2006 6/2/2006 8

--
Rose


Steve Schapel said:
Rose,

This is not a job for a Macro. It is a calculation, which can be
performed either in a Query, or in the Control Source of a textbox on
your Form or Report. One way to get the last day of the month is via
the DateSerial function, for example...
DateSerial(Year([Check In Date]),Month([Check In Date])+1,0)-[Check In
Date]
.... will give the number of days in March if the Check In Date is in March.

--
Steve Schapel, Microsoft 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 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.
Click to expand...
Click to expand...
 
Rose,

You mean like this?...
JuneNights: Nz([Check Out],#6/30/2006#)-IIf([Check
In]<#6/1/2006#,#6/1/2006#,[Check In])
 
Back
Top