G’day,
I am converting my 10 yr old Access Book Collection to Libre Office Base (5.1). I have successfully moved all the data and have also closely replicated the main form.
I have a small problem which is driving me nuts. Its an Age query which fills a text box on the main form.
I have trolled through all the forums regarding age calculation which I can do quite easily using the Birth Year and the present year. I might add at this point that I do not need accuracy down to the month and day, I am working in years.
My problem is that not all the authors are alive so I also have in my table a Death Year.
In Access I used an “IIf(IsNull” Expression, which I have pasted below:
SELECT tblAuthor.AuthorID, tblAuthor.YearofBirth, tblAuthor.YearofDeath, DatePart(“yyyy”,Date()) AS ThisYear, IIf(IsNull(tblAuthor!YearofDeath),Sum([ThisYear]-[YearofBirth]),Sum(([YearofDeath]-[YearofBirth]))) AS Age
FROM tblAuthor
GROUP BY tblAuthor.AuthorID, tblAuthor.YearofBirth, tblAuthor.YearofDeath;
I am not sure if this can be replicated in Base however, I think I have come close using a “Case Where” expression which is pasted below:
SELECT “AuthorsID”, “Author”, “YearofBirth”, “YearofDeath”, TO_CHAR( CURRENT_TIMESTAMP, ‘YYYY’ ) AS “ThisYear”, CASE WHEN “YearofDeath” IS NULL THEN “ThisYear” - “YearofBirth” ELSE “YearofDeath” - “YearofBirth” END AS “Age” FROM “tblAuthorsDetails”
NOTE: The table name has changed but data has not.
When I try to run the query I get an error message saying the data cannot be loaded. I would appreciate any assistance.
Regards
Gazza