days vacant

[GetReel]

I took over an old DBs that is used to track tenants.

The person who created it, no longer work for the dept . He created this query that calculates the days a property has been occupied.
DaysOccupied: IIf(IsNull([DateOut]),DateDiff("d",[DateIn],Date()),DateDiff("d",[DateIn],[DateOut]))

To calucated the total days in a subform I added =Sum([DaysOccupied])

what I need or would if knew how is to do a similar one that caculates the vacant days.

The above formula is little complex for me so I was hoping some could help me out.

 

[RichO]

I'm guessing that the number of vacant days would be calculated by counting the number of days between [DateOut] and today's date?

You can use the same type of expression as the other calculation...

DaysVacant: IIf(IsNull([DateOut]), 0, DateDiff("d",[DateOut],Date()))

So if [DateOut] is null, meaning the tenant has not left, the result would be 0. Otherwise the result is the difference between DateOut and today.

 

[GetReel]

Thanks Richo!

works nicely but if we wanted to calculates the vacant between when someone departs and some else enters the property.

effectively that formula is still counting a vacant days even if some else is residing the flat . I need it to somehow calculate the difference between last date out with the newest DateIn.

Then I use the a calculated sum in the footer of the subform and display on the main form.

Ive attached a copy of the main form as an example

Does this make sense.

 

[RichO]

If a new tenant moves in, is the [DateIn] field entered in the same record as the [DateOut] from the previous tenant? And when a tenant vacates the flat, is the [DateIn] field cleared?

If this is the case, you can check for the existence of data in [DateIn] and calculate days vacant accordingly:

DaysVacant: IIf(IsNull([DateOut]), 0, IIf(IsNull([DateIn]), DateDiff("d",[DateOut],Date()), DateDiff("d", [DateOut], [DateIn]))

See if that is what you need.

 

[GetReel]

No. Everytime a trenant is booking in, they are booked in a new record because they may be more than one tenant in the property ie single people sharing. So idealy, I only want to count those properties where there are no tenants occupying a property. Where tenants share ie
1 departed say 01/05/04 and another tenants entered the property 3/04/04, the the formula wont work, right.

 

[RichO]

1 departed say 01/05/04 and another tenants entered the property 3/04/04, the the formula wont work, right.

I don't follow. Shouldn't days vacant be the number of days between 1/5/04 and 3/4/04 in the above example?

 

[GetReel]

Yes you are right in thinking that. My apology. Wrong example .

I'll keep working to the examples you provided..its been very helpful.