Page 1 of 1

[Solved] Calculating age

PostPosted: Tue Sep 16, 2014 3:23 pm
by crainneag
Hi, can anyone help me with a technique to calculate a person's age from their date of birth in Base? I would like it to be present in a table and form so that it updates year on year.

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 4:12 pm
by Villeroy
http://www.hsqldb.org/doc/1.8/guide/ch0 ... ed-section
DATEDIFF('year',"Date Field",CURRENT_DATE)

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 4:19 pm
by Sliderule
Since you have NOT indicated which database back-end ( engine ) you are using . . . I will assume you are using HSQL . . . and . . . this technique can be used either with the Embedded Database engine ( HSQL 1.8.0.10 ) as the date ( database pun intended ) I am writing this, OR, a more advanced version of HSQL ( such as HSQL 2.3.2 ).

Additionally, I am assuming, you desire a result as an INTEGER ( not a decimal ). So, for example, if today is '2014-09-16 and the contents of your table has a field, with a name of, "DATE_OF_BIRTH" and the values for that field are:

  1. '1999-09-15' it will return 18
  2. '1996-09-16' it will return 18
  3. '1996-09-17' it will return 17
On the Field line while creating your Query . . . be sure to change "DATE_OF_BIRTH" to whatever is the field in your table for the date of birth:

Code: Select all   Expand viewCollapse view
YEAR(CURRENT_DATE) - YEAR("DATE_OF_BIRTH") - CASEWHEN(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH("DATE_OF_BIRTH")*100 + DAYOFMONTH("DATE_OF_BIRTH"),0,1)

Explanation: Since, you NEVER should define the age as a field in a database table, since, it can change, it should be calculated when needed. The above formula, will exactly that. You can, of course, define a VIEW with the row contents, and, this calculation. :)

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit buton top right) if this is resolved.

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 4:23 pm
by Sliderule
WARNING . . . the calculation displayed by Villeroy above ( using DATEDIFF ), will, provide INCORRECT values. Try it with the examples ( values ) I described in my post above to 'see' them.

Sliderule

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 5:46 pm
by crainneag
Thanks to both of you, I will check the preferred method once I geet access to my paper based source data, As I read in a csv file from another database Base is arguing about me about the way dates have been transferred. I will get back to you on this presently.

You say Datediff will produce erroneous results, will it be a consistent, predictable error or a variable one?

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 6:04 pm
by Sliderule
crainneag wrote:You say Datediff will produce erroneous results, will it be a consistent, predictable error or a variable one?

Yes, it is "predictable" . . . BUT . . . I do not understand your question. Since it is in error ( wrong results to calculate an age, depending on the MONTH and DAY ) why would you EVER use it to calculate an age, in years ? Put another way, the "predictability" of an error, is totally irrelevant.

Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this is resolved.

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 6:16 pm
by crainneag
I ask because I came across a similar problem using Lotus Approach where I used this formula to calculate age from a DOB and display it on the form "If(Year(Today() - patients.DOB) - 1 < 120, Year(Today() - patients.DOB) - 1, 0)". This produced a constant error of one year over the true value and was corrected by subtracting 1, that is why I asked about the nature of the error, if it is constant then adding a negative of the same value will eliminate it.

Re: Calaculating age

PostPosted: Tue Sep 16, 2014 8:16 pm
by Sliderule
The reason you had that error, with the formula you described using with Lotus Approach ( or any other database for that matter ) . . . is because it ( the formula ) does NOT take the current year, the current month, and, the current day, compared to the month and day ( and of course year too ) of the "DATE_OF_BIRTH" date value.

That is exactly, why, the formula ( calculation ) I provided does work, as, I think you want it. If not, let me / us know.

If you upgrade to a SPLIT database, using HSQL ( external to OpenOffice / LibreOffice Base ) . . . for example . . . HSQL 2.3.2 . . . ( NOT the embedded database, HSQL 1.8.0.10 ) I can provide you several user defined functions, so, you can have it ( the database back-end ) calculate AGE as an INTEGER, a DECIMAL, or, a FLOAT number ( 17 decimal place accuracy ), by just entering two dates, for example:

Code: Select all   Expand viewCollapse view
AGE("DATE_OF_BIRTH", CURRENT_DATE)

AGE_DECIMAL("DATE_OF_BIRTH", CURRENT_DATE)

AGE_FLOAT("DATE_OF_BIRTH", CURRENT_DATE)


Sliderule

Thanks to ad [Solved] in your 1st post Subject (edit buton top right) if this is resolved.

Re: Calaculating age

PostPosted: Sat Sep 20, 2014 5:21 pm
by however
Hi,

Sliderule wrote:Since you have NOT indicated which database back-end ( engine ) you are using . . . I will assume you are using HSQL . . .

Since I am interested in adding this little feature to my project, I find it unnecessary to open a new thread with the same title so, hoping that no one would mind, I would like to jump in and ask this:

how/where would it change if I used MySQL as back-end, instead of HSQ?

Regards

Re: Calaculating age

PostPosted: Sat Sep 20, 2014 5:44 pm
by Sliderule
however wrote:how/where would it change if I used MySQL as back-end, instead of HSQ?


With MySQL as your database back-end . . . use the following formula, to calculate AGE as an integer, and, `DATE_OF_BIRTH` is the column name in your table ( `DATE_OF_BIRTH` is used three times in formula below -- change to your table column name :super: ) .

Code: Select all   Expand viewCollapse view
YEAR(CURRENT_DATE) - YEAR(`DATE_OF_BIRTH`) - IF(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH(`DATE_OF_BIRTH`)*100 + DAYOFMONTH(`DATE_OF_BIRTH`),0,1)

Please do me a favour, let me / us know how the above works for you, when using MySQL . . . that is . . . does it meet your needs, or, not really. :)

Sliderule

Re: Calaculating age

PostPosted: Wed Sep 24, 2014 2:16 pm
by however
Sliderule wrote:Please do me a favour, let me / us know how the above works for you, when using MySQL . . . that is . . . does it meet your needs, or, not really.


It works like a charm!creaed a query in SQL view, copied&pasted your calculation code and changed the Date_Of_Birth name with my Birthdate corresponding name and magic happened.

However, if I may sound really silly, I am struggling now to actually add this new column "Age") in the form.

So far I have:
1) added a new 'Age' column to the 'Patient' table, and marked it as INTEGER;
2) created a 'qry_age' and run it, successfully,
3) edited the 'Patient Details' form by adding a 'Age' list box and linking it the 'qry_age', created previously,

when I open the 'PatientDetails' form, no age is shown on the newly added listbox. I have the same result if instead of linking the 'Age' listbox to the 'qry_age', i link it to an SQL command (the calculation).

I havent tried to create a 'Age' subform and adding it to the original 'PatientDetails' form. Would it be the correct way of doing this?

What am i missing?

Re: Calaculating age

PostPosted: Wed Sep 24, 2014 2:27 pm
by Villeroy
The information about the age is stored in the date field. Simply do not store any calculated values. Never. Now you store an age of 56 for someone. Next year he is 57 but your age field keeps a stored age of 56.

Re: Calaculating age

PostPosted: Wed Sep 24, 2014 2:39 pm
by however
Villeroy wrote:The information about the age is stored in the date field. Simply do not store any calculated values. Never. Now you store an age of 56 for someone. Next year he is 57 but your age field keeps a stored age of 56.

am i right to understand that I should not have a 'Age' column in the 'Patient' table? but rather use the calculated age from the query?

Still quite confused though, on how i'm going to have it to work on the form, as I have just tried adding a 'AgeSubform' in the 'PatientDetails' form, linkying it to the 'qry_age' and still with the same result: no age.

Re: Calaculating age

PostPosted: Wed Sep 24, 2014 2:44 pm
by however
[
however wrote:Still quite confused though,

the coin dropped just as I clicke on submit.
I have solved by replacing the list box with a formatted field.
Thank you.
p.s.: unfortunately, I cannot add SOLVED to the post, as it wasn't mine initially, but I'd be happy to add it to my section if that's the correct way of doing it.

Re: Calaculating age

PostPosted: Wed Sep 24, 2014 2:46 pm
by Villeroy
No, you must not have an age field in a table of persons.
On the form you do not "work" with the age. You display the age that results from the stored birth date.

SELECT *,
YEAR(CURRENT_DATE) - YEAR(`DATE_OF_BIRTH`) - IF(MONTH(CURRENT_DATE)*100 + DAYOFMONTH(CURRENT_DATE) >= MONTH(`DATE_OF_BIRTH`)*100 + DAYOFMONTH(`DATE_OF_BIRTH`),0,1) AS `Age`
FROM `Patients`

selects all the fields ( * ) and the calculated age from the patients table. You can use this query in the exact same manner as the table itself. The age can not be editied. If the age of a patient is wrong, then you've got to correct the birth date.

Re: Calaculating age

PostPosted: Wed Sep 24, 2014 3:54 pm
by Sliderule
however:

Thank-you for both understanding correctly, that the calculation / field is NOT a part of the table, but, yes, it can be either in a Query, or, a View. This way, regardless of a Date change . . . for example, the CURRENT_DATE changes, whether with a year of 2004, 2014, or, 2024, the CALCULATED value of "AGE" will be changed. :)

And, however, I thank-you for indicating in the this forum, that the 'calculation' is correct, in your case, when using MySQL as your database back-end, so, other forum users may use it ( calculation of AGE )/find it too. :)

I am however ( use of the word however is a pun with your forum name :) ) still waiting for the forum Original Poster to modify the forum's top title to [Solved] so when other users search the forum, they will find a usable result more easily.

Sliderule