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" wrote:
[Quoted Text] > 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
>
> Rose wrote:
> > 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.
> >
>
|