Ask Your Question
0

Age calculation using BirthDate,DeathDate and Now

asked 2016-09-05 14:39:46 +0200

Gazza gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2016-09-05 15:49:22 +0200

Ratslinger gravatar image

Just use the extracted year in the calculation:

SELECT "AuthorsID", "Author", "YearofBirth", "YearofDeath", CASE WHEN "YearofDeath" IS NULL THEN TO_CHAR( CURRENT_TIMESTAMP, 'YYYY' ) - "YearofBirth" ELSE "YearofDeath" - "YearofBirth" END AS "Age" FROM "tblAuthorsDetails"
edit flag offensive delete link more

Comments

Thanks Ratslinger. That did the trick. I had spent days trying to work that out. Much appreciated.

Gazza gravatar imageGazza ( 2016-09-06 01:13:42 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2016-09-05 14:39:46 +0200

Seen: 62 times

Last updated: Sep 05 '16