Generate Age from Date of Birth

[Guest]

I have designed a Form that when the Date of Birth is entered the age is
automatically generated in Years and Part year EG: 21.4 (Dec)imal)
What I would like is that the Age be generated in Years and Months EG: 21
Years 5 Months. Is there a way this can be done in access?

Thanks for any help

 

[Dirk Goldgar]

I have designed a Form that when the Date of Birth is entered the age
is automatically generated in Years and Part year EG: 21.4 (Dec)imal)
What I would like is that the Age be generated in Years and Months
EG: 21 Years 5 Months. Is there a way this can be done in access?

Thanks for any help

http://www.accessmvp.com/djsteele/Diff2Dates.html
A More Complete DateDiff Function

 

[Guest]

Thanks for the information Dirk. Do I need to generate all the code shown
here or only part of it? Sorry to be such a nuisance and Thanks

 

[Dirk Goldgar]

Thanks for the information Dirk. Do I need to generate all the code
shown here or only part of it? Sorry to be such a nuisance and Thanks

Copy everything between the "*** Code Start ***" and "*** Code End ***"
lines, and paste it into a standard module. If you create a new module
for the purpose, don't name the module the same as the function.

With that function in place, you can use it on your form, or in the
query your form is based on. The person's age, as calculated by the
function, shouldn't actually be stored in the table, because it changes
from day to day and can always be calculated from the date of birth.
Instead, use a calculated field in the form's recordsource query, or a
calculated control on the form, to compute and display the age for each
record.

In a query, you would use a calculated field defined (in query design
view) like this:

Age: Diff2Dates("ym", [Date of Birth], Date(), False)

Then the calculated Age field would be available in your form's field
list and you could bind a text box to it.

Or you could use a calculated text box on the form, setting its
ControlSource property to:

=Diff2Dates("ym", [Date of Birth], Date(), False)

Does that clarify how to use the function? I've taken your word for it
that you want to show years and months, and not years. months, and days,
which is also possible with that function.

 

[Guest]

Yes Dirk, Just Yrs and Mths. Not very familiar with Modules but will give it
a go and get back to you if I do not succeed. I could spend a few hours with
my knowledge, but sometimes it is a good way to learn by trying it yourself
and not relying on others to always spoon feed you. Thanks for your patience
and invaluable help.

Thanks for the information Dirk. Do I need to generate all the code
shown here or only part of it? Sorry to be such a nuisance and Thanks

Copy everything between the "*** Code Start ***" and "*** Code End ***"
lines, and paste it into a standard module. If you create a new module
for the purpose, don't name the module the same as the function.

With that function in place, you can use it on your form, or in the
query your form is based on. The person's age, as calculated by the
function, shouldn't actually be stored in the table, because it changes
from day to day and can always be calculated from the date of birth.
Instead, use a calculated field in the form's recordsource query, or a
calculated control on the form, to compute and display the age for each
record.

In a query, you would use a calculated field defined (in query design
view) like this:

Age: Diff2Dates("ym", [Date of Birth], Date(), False)

Then the calculated Age field would be available in your form's field
list and you could bind a text box to it.

Or you could use a calculated text box on the form, setting its
ControlSource property to:

=Diff2Dates("ym", [Date of Birth], Date(), False)

Does that clarify how to use the function? I've taken your word for it
that you want to show years and months, and not years. months, and days,
which is also possible with that function.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Yes Dirk, Just Yrs and Mths. Not very familiar with Modules but will
give it a go and get back to you if I do not succeed. I could spend
a few hours with my knowledge, but sometimes it is a good way to
learn by trying it yourself and not relying on others to always spoon
feed you. Thanks for your patience and invaluable help.

You're welcome. Come on back if you run into problems you can't solve.

 

[Guest]

Thanks again Dirk. Just wanted to let you know, after some trial and error
it works, thanks to you. One thing though is if there is no Date of Birth
recorded, it shows "error" in the Age field. Is there any way around this?
Thanks

 

[Dirk Goldgar]

Thanks again Dirk. Just wanted to let you know, after some trial and
error it works, thanks to you. One thing though is if there is no
Date of Birth recorded, it shows "error" in the Age field. Is there
any way around this? Thanks

I suspect that you could avoid that problem by changing the function
declaration from:

Public Function Diff2Dates( _
Interval As String, _
Date1 As Date, Date2 As Date, _
Optional ShowZero As Boolean = False) _
As Variant

to:

Public Function Diff2Dates( _
Interval As String, _
Date1 As Variant, Date2 As Variant, _
Optional ShowZero As Boolean = False) _
As Variant

 

[Guest]

Yes Dirk, it worked a treat. You knowledge never ceases to amaze me.
Many thanks