Calculate Age from DOB field

[Corne]

Hi,

I have seen so many threads on this instance, but don't understand half of them.

I have a grid with a detail view.
In the detail view I have a date DOB date field [populated with a date picker]
I also have an Age field [tried with c,d or n field types] in the same detail view.

How do I calculate my current age from now() and have the result in the Age field. Result must be either in years, months, day or could be converted to a number for validation. [Example: age could be 16.5 years and validate as >16

NB!!!
This Age result must not be static, but updated each and every time the Age field gets focus. The Age will be used to perform validations against other fields in the grid.

I am trying to validate the age against:
If my age is >16 [years of age] then "true,false"

[mikeallenbrown]

Re: Calculate Age from DOB field

Assuming you are using SQL as your back end you can do the calculation there.

DOB - datetime

age - (datediff(day,[DOB],getdate())/(365))

When the user saves the record the age will then be calculated.

[Corne]

Re: Calculate Age from DOB field

Thanks Mike, where do I run this from exactly?

[mikeallenbrown]

Re: Calculate Age from DOB field

Thanks Mike, where do I run this from exactly?

Below is a pic of SQL 2008 of how you'd set this up.

[GGandhi]

Re: Calculate Age from DOB field

hello
you did not state your using dbf or sql
in dbf add a calculated field dob(date_of_brith)
in sql use sql statement for the grid and in the sqlstatment builder add an expression addYearsToDate(date_of_birth,currentDate()) as ageInYears
lot easier than you think.
if still having problem post back.

ps: in the meantime mike has posted his solution, mine does not store any age in the table. just calculates on the fly. you can store the age as he suggested if that is what you need.

[Corne]

Re: Calculate Age from DOB field

Thanks for the input, I am trying both options.

I have 1stly tried mike's code on sql [thanks mike] but get an "argument is incorrect data type" error in my browser. I have changed the sql field to date, datetime, varchar and updated the grid each time with these new sql field types, but still the error.

Mike, what is the data type in sql and should I change anything in the grid to make this work.

GGhandi,
in the sql expression, how does it know to which field or rather how do I get the value to the age field?

[Corne]

Re: Calculate Age from DOB field

Never mind, found my typo. easy in the end.

Now, in order as to validate other fields against this age, I need to know EXACTLY how old I am. If I am 2 days older than 47 then I will be excluded from program options. How do you propose thius to be done.

I think maybe to convert this age to a number representing years/days/months [47.23] and validating against the number.

[GGandhi]

Re: Calculate Age from DOB field

here is a screencast to show how to calculate the days from date of birth and use that value.
i am just showing in general you can work to fit your need.

if you described in the first post itself what you want as you did in the post 7 you would gotten the answer from one of the people earlier.
take a look at this video

http://screencast.com/t/WLhJWzOl

[Skipperj]

Re: Calculate Age from DOB field

The 365 in the first equation has to be 365.25 to take care of leap years. even then it is only almost dead accurate.

(now - then) in days / 365.25

[Corne]

Re: Calculate Age from DOB field

Thank you all so much. I believe I have it working now. Skipper, changed the SQL code to 365.24