Need help with an average query

[Joe Cilinceon]

What I have is a table called Leases it contains a start date and end date.
What I want to do is get the average length of the lease from the start date
to the end date and if the end date is null I want to use the current date.

 

[Joe Cilinceon]

What I have is a table called Leases it contains a start date and end
date. What I want to do is get the average length of the lease from
the start date to the end date and if the end date is null I want to
use the current date.

I solved with Nz(EndDate,Date())

 

[Rick Brandt]

What I have is a table called Leases it contains a start date and end
date. What I want to do is get the average length of the lease from
the start date to the end date and if the end date is null I want to
use the current date.

SELECT LeaseID, Avg(DateDiff("d", [Start Date], Nz([End Date], Date()))) As Days
GROUP BY LeaseID

 

[Rick Brandt]

What I have is a table called Leases it contains a start date and
end date. What I want to do is get the average length of the lease
from the start date to the end date and if the end date is null I
want to use the current date.

SELECT LeaseID, Avg(DateDiff("d", [Start Date], Nz([End Date],
Date()))) As Days GROUP BY LeaseID

DOH! Obviously the LeaseID and Group By above should not be there.

 

[Joe Cilinceon]

What I have is a table called Leases it contains a start date and
end date. What I want to do is get the average length of the lease
from the start date to the end date and if the end date is null I
want to use the current date.

SELECT LeaseID, Avg(DateDiff("d", [Start Date], Nz([End Date],
Date()))) As Days GROUP BY LeaseID

DOH! Obviously the LeaseID and Group By above should not be there.

Actually it took me 3 queries to get what I wanted, they are listed below.

LGTHOFSTAY1
SELECT LEASES.StartDate, LEASES.EndDate,
DateDiff("m",[StartDate],Nz([EndDate],Date())) AS LengthOfStay
FROM LEASES;

LGTHOFSTAY2
SELECT Avg(LGTHOFSTAY1.LengthOfStay) AS AvgOfLengthOfStay
FROM LGTHOFSTAY1;

LGTHOFSTAY3 (Which is what I wanted)
SELECT LGTHOFSTAY2.AvgOfLengthOfStay, Int([AvgOfLengthOfStay]/12) AS Years,
Int([AvgOfLengthOfStay]-([Years]*12)) AS Months, Str([Years]) & " years " &
Str([Months]) & " months" AS AvgLengthOfStay
FROM LGTHOFSTAY2;