How to find Voids in data?

[madwildfire]

Hi All,

I'm creating a database for managing the Leases of a number of different properties, these leases typically run for six months.

As such I have three tables : Tenants, Properties and Leases.

What I'm struggling to create is a query or vba script that can, for a given start and end date, tally the number of days that a property has not been leased.

Could anyone point me in the right direction on what I could research to accomplish this?


Many thanks,



Will

[ItsMe]

Maybe the datediff() function?

dim strgiven_start as string
dim strend_date as string
dim intAnswer as integer
intAnswer = DateDiff("d", strgiven_start, strend_date)
msgbox intAnswer

[ranman256]

Yes or put it in a query:

Select *, DateDiff("d", strgiven_start, strend_date) as Days from tLEASES

[madwildfire]

Thank you both for your answers, your methods absolutely allow calculating the period of the lease.

I did not provide a great deal of detail in my opening post. What I'd like to tally is the number of days for which a lease record does not exist, i.e. the period the property does not have a corresponding Lease record.

How can we total this 'missing' data?

Edit_
Looking into how I can go about counting from the end (EndDate field) of one record and the start (StartDate field) of the next record, but within a specified date range.

[ItsMe]

If it was me I would do the whole thing in VBA and build the results to a temp table. Using DAO, I would grab the first unit and its End Date. I would go through that units history using findfirst in a second DAO recordset, sorting start dates ascending. I would calc the date diff and add it to a variable of integer type. At the end of looping through the first unit's history I would write the value of the integer and the corresponding unit ID to my temp table.