Using DateDiff on a query to calculate roof life

[philbill]

I have a table tblRepairs with fields for address, RepairDate and RepairType. Values for RepairType are entered via combo box: NewFlat, NewShingle, RepairFlat, RepairSingle. Repair dates go back to the mid 1990's so I have duplicate address to account for these values. For example: each of these addresses may have dates for each NewShinlge,RepairShingle, NewFlat, another NewShingle (indicatiing a new roof).

I want to be able to calculate roof life between NewShingles and NewFlats for each address.

Haven't a clue how to begin.

I'm using Windows XP and Access 2002

Help would be greatly appreciated.

Thank you

[ranman256]

In a query calc the life of the tile...
Select DateAdd("yyyy",[LifeOfTiles],[InstallDate]) as ExpireDate from table

[philbill]

Thanks for your response ranman. I played around with your suggestion and experimented to come up with this query (SQL);

SELECT Repair.UnitID, Max(Repair.RepairDate) AS MaxOfRepairDate, Last(Repair.RepairDate) AS LastOfRepairDate, Repair.RepairType, DateDiff("yyyy",[LastofRepairDate],[MaxofRepairDate]) AS RoofLife
FROM Repair
GROUP BY Repair.UnitID, Repair.RepairType, DateDiff("yyyy",[LastofRepairDate],[MaxofRepairDate])
HAVING (((Repair.RepairType)="Flat Roof New"));

I did the same for Shingle Roofs and have what I want for the most part. Agan thanks for your response.

[ssanfu]

Just a word of warning. The "Last()" function returns the last record in the recordset, not the earliest date.
I don't know your table structures or relationships, but really check that the dates are what you need/are correct. Most people that use the "First()" and "Last()" functions don't really understand what they return.


Good luck with your project......