Macro to calculate days in a month

[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.

 

[Steve Schapel]

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.

 

[Guest]

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

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.

 

[Steve Schapel]

Rose,

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