Query Limitations

[Bonifaceg]

Hello all. I have a query that returns a tally of days stayed for clients at a residence. There can be multiple stays. I need to know if there is a way to calculate only the number of days stayed over the last 366 days.

eg:
Client A: IN:Jan 01 2004 OUT:Mar 28 2004
IN:Apr 01 2004 OUT:Jun 30 2004
IN: Dec 01 2004 OUT:Mar 30 2005
For client A, I would need the tally of his days stayed only since Apr 13 2004. How would one go about this? Any & all help would be welcome.
Regards,
Bonie.

 

[Sergeant]

> DateAdd("yyyy",-1,Date)
or
> Date - 366

 

[Bonifaceg]

In-Between Dates for Query

Thanks for the reply, Sergeant.
To Clarify:
Unless I'm mistaken, the IN dates are the ones on which the criteria would be applied. Going back to the initial post, for the period

IN:Apr 01 2004 OUT:Jun 30 2004,

would the days stayed be counted from the 13th of April onwards, or would that whole batch of days not be counted (since "IN date" < "criteria")?

 

[Sergeant]

You are right. I read your post too quick. I have to run off to work right now...I'll give it some thought. (Perhaps someone else already has this worked out?)
I'm thinking you will need a calculated field for each record for duration of stay, but still have to think how to pick up just the portion of a stay that was within the past year. (ie...checked in 400 days ago...checked out 300 days ago...only stayed 66 days in the past year)

 

[ColinEssex]

In your query in the DateIn criteria 1st line put this -

Between Date()-366 And Date()

then on the 2nd line of the DateOut criteria put this -

>Date()-366

then in a new calculated field in the query put this -

IIf([DateIn]<Date()-366,DateDiff("d",Date()-366,[DateOut]),DateDiff("d",[Datein],[Dateout]))

basically the first bit of code picks up those where the in date is between today and 366 days ago.

the 2nd bit of code picks up those where the end date is between today and 366 days ago (irrespective of the start date)

the 3rd bit of code works out the days between the in date and end date OR if the in date is >366 days back, it calculates from 366 days back to the end date.

Col