Calculating YMD from a date of birth

[DubboPete]

Hi all,

Trawling thru the newsgroup don't give me an easy answer - how do I
calculate the difference on a form between a Date Of Birth (say, 25 Mar
2005) and Now(), and get a field to display it as "0 years, 11 months and 21
days" ?

I know it's not going to be easy.... but any help appreciated

DubboPete
17th century goat-herder

 

[AkAlan via AccessMonster.com]

There are a couple of ways to do this, with VBA or something like this:

Create an unbound text box on the form.
Make the recordsource look like this:

= DateDiff("Y",DateofBirth,Date()) & " Years, " & DateDiff("M",DateofBirth,
Date()) _
& " Months, " & DateDiff("d",DateofBirth,Date()) & " Days"

This is assuming there is a row called DateofBirth in the forms query.
I use Date() rather than Now() since you only need the Date and not the Time.


I'm not sure what you will get if the birth date is less than a year from the
current date but if it gives you something funky try this for the first part:

=IIF(DateDiff("y",DateofBirth,Date()) < 1, "0",DateDiff("y",DateofBirth,Date()
)

 

[Douglas J Steele]

Take a look at http://www.accessmvp.com/djsteele/Diff2Dates.html

 

[AkAlan via AccessMonster.com]

Correction, I got to work and tried this out and realized you need to put
"yyyy" for the year not just "y".
If there is less than a year, or month you will get a zero so no need for the
IIF function

There are a couple of ways to do this, with VBA or something like this:

Create an unbound text box on the form.
Make the recordsource look like this:

= DateDiff("Y",DateofBirth,Date()) & " Years, " & DateDiff("M",DateofBirth,
Date()) _
& " Months, " & DateDiff("d",DateofBirth,Date()) & " Days"

This is assuming there is a row called DateofBirth in the forms query.
I use Date() rather than Now() since you only need the Date and not the Time.

I'm not sure what you will get if the birth date is less than a year from the
current date but if it gives you something funky try this for the first part:

=IIF(DateDiff("y",DateofBirth,Date()) < 1, "0",DateDiff("y",DateofBirth,Date()
)

Hi all,

[quoted text clipped - 7 lines]

DubboPete
17th century goat-herder

 

[DubboPete]

Hi Douglas,

I took a look, and supposed I botched where I am supposed to hook into the
function. This bit really gets me every time:-(

I created a new module, called it GetMyDate, pasted in the function... and
now I don't know where to go next to call the function. Is it on the form's
On Current property, or somewhere related to the field?

Sorry for acting dumb, but I'm sure with a little 'friendly' guidance, I can
overcome this gigantic obstacle!

DubboPete
ps. for AkAlan, your code produced the following result, 0 years, 11 months,
355 days - not quite the desired result!

 

[Douglas J. Steele]

You can add a textbox to the form and make its ControlSource

=Diff2Dates("ymd", DOB, Date)

 

[DubboPete]

almost there... so I looked closer, then I went in and changed that syntax
to

=Diff2Dates("ymd",[DOB],Date())

(note the () after Date) and now it works perfectly!

Thanks for your help Douglas. A sterling job

DubboPete