...fill missing dates between date range

[Colores]

Hello, I work in a charity who help people in need with temp accommodation (houses or units).

I need to add vacant periods for each property to calculate the vacancy rate. The yellow rows in my sample are the ones to be added by vb in a table called Lease.

I have a sub-form who show periods in Lease for that property.

We want the VACANT records to be display too.
Thanks millions in advance - I been going crazy about this for days.
Thanks Colo

[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 85[/IMGnothumb]
[IMGnothumb]https://bytes.com/attachment.php? attachmentid=85 86[/IMGnothumb]

[zmbd]

In your first image I see what looks like an excel/workbook file - is that correct, and now you are trying to do the exactly the same thing in Access that you were doing in the worksheet?

You can do this by expansion query and a few other means; however, if I may:

+ One doesn't normally store a calculated value such a s [RentLength] in the database.
Instead, this is calculated "on the fly" in the query using a calculated field, or a similar concept with a calculated control on a form or report.

+ I notice that your start and end dates for the rental period appear to coincide with the start end dates of the vacant periods so to take your first few rows:
rented from 2012-01-11 thru 2013-11-28
vacant from 2013-11-28 thru 2014-06-30
rented from 2014-06-30 thru 2015-02-23

Thus, you should have all of the information needed to calculate the vacancy directly:

So what I would do is take your earliest rent start date of the period in question (2012-01-11) and the oldest rent end date (or current date) calculate the total days between the two. (you don't have one in the first image so today's date 2015-12-21 which gives 1440 days) sum the calculated rent length from records
Note there appears to be something off in the first image:
2012-01-11 thru 2013-11-28 = 687days
2014-06-30 thru 2014-10-02 = 97days
2015-02-23 thru 2015-07-27 = 519days
2015-11-15 thru today = 36days
Sum is 1339
(1339/1440)*100 = 92.99% occupancy == 7.01% vacancy

This can also be done in the worksheet.

[Rabbit]

Your result set doesn't make sense.

Why does the second vacancy overlap with an existing lease? And what algorithm do you use to determine that a lease doesn't count towards occupancy?

Also, why does your vacancy range overlap with the end of the previous lease and start of the next lease? If seems to me that you run the risk of having 2 groups of people in the same lease on the same day unless you include some sort of time element.

[zmbd]

Good catch Rabbit, I didn't take that close of a look at the second data-set shown - instead focusing on the first... I have a feeling that there are other issues here yet to be discovered :)

[Colores]

Thanks for the quick response and help. I try to do this in Access (i copy my query result into Excel only to make a sample).

I need to add those VACANT records into the Lease table. I highlight in yellow the records to be added. Rabbit thanks for pointed the overlapping in the dates.
Thanks guys

[Colores]

Public Function FillVacancy(PropertyID As String, TenantID As String, DATEEFF As Long)

  Dim dbs As Database
  Dim rst As Recordset
  Dim strSelect As String

  strSelect = "SELECT * FROM Lease WHERE [PropertyID] = " & "'" & PropertyID & "'"

  Dim datMin  As Date
  Dim datMax  As Date
  Dim datLog  As Date
  Dim datNew  As Date
  Dim datOld  As Date
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSelect)
  With rst
    ' Find first and last logging date.
    .MoveLast
    If Not IsNull(!EndDate.Value) Then
                            datMax = !EndDate.Value
                            Else
                            datMax = Now
    End If
    .MoveFirst
    datMin = !StartDate.Value
    ' Set initial low value of empty interval.
    datOld = datMin
    ' Set initial high value of current empty interval.
    datLog = datMin
    .MoveNext
    ' Locate empty intervals.
    ' Stop when reaching the last of the old records.
    While .EOF = False And [PropertyID] = PropertyID And datLog < datMax
      ' Set high value of a possible empty interval.
      datLog = !StartDate.Value
      ' Increment low value of possible empty interval by one hour.
      datNew = DateAdd("h", 1, datOld)
      ' Fill empty intervals with hourly recordings of Null values.
      Do While DateDiff("h", datNew, datLog) >= 1 And datNew < datMax
        .AddNew
          !PropertyID.Value = PropertyID
          !LeaseID.Value = "VACANT"
          !TenantID.Value = TenantID
          !DATEEFF = DATEEFF
          !StartDate.Value = datNew
        .Update
        
        datNew = DateAdd("h", 1, datNew)
        
      Loop
      ' Set low value of the next possible empty interval.
      datOld = datLog
      .MoveNext
    Wend
    .Close
  End With
  
  Set rst = Nothing
  Set dbs = Nothing

End Function

[Colores]

I starting with this code but it got too confused and I don't think I am in the right path. too messy

[Rabbit]

Please use code tags when posting code or formatted data.

There's no need for any code, you can do it in just SQL.

What you do is join the table to itself on the leases that occur after the end of the lease.

The end date plus one day from the first reference becomes the start date of the vacancy.

You take the minimum date of the start date from the second table and subtract a day as the end of the vacancy.

And in the having clause, you filter out those that start right after each other.

Also, I agree with zmdb, you should just keep the results as a query. It's a bad idea to store it in the table.