Accurate Age Calculation

N

Negger

Registered User.
Local time
Today, 06:36
Joined
Feb 3, 2011
Messages
25
Hi, I've found lots of info, here & elsewhere, on age calculation from a DOB field - however, very little detailed and precise instructions as to how to make it all work !
I've found the following code on here:

Age: DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB])))

I have a Form with a DOB field linked (bound) to a Table. On the Form I also have an un-bound Text Box called Age (I'm deliberately ommitting any " " so as to avoid any confusion in the code).
I know not to store the calculated age in the Table, hence the un-bound box.
Could someone please explain precisely how & where I enter this code on the Form to get the Age field to automatically show the precise age (YYMM) or (YYMMDD) upon opening the Form e.g.
Design View/Property Sheet/ Tab?/etc.
If not obvious, it may be useful to know whether this is a Macro, or VBA Code or an Expression ? Many of the solutions I've looked at aren't clear on this point.
Many Thanks in anticipation

Negger
 
What you are showing is an Expression.

In your unbound text box this would become the control source, make sure the field names match yours. Then you adjust the format from the format property.
 
Dear Trevor
Many thanks for taking the trouble to reply.
I pasted the code as is into PropertySheet/Data/Control Source but got a #name? error.
I've formatted the Age Control as Shortdate.
Upon investigation I've deleted the Age: from the start of the code and inserted an = with no spaces (This is what I meant in my first post about precise- It may seem obvious to experts, but I assumed that Age: was part of the code !)
I'm now getting a display of 19/01/1900 in my Age Control, which is more than a formatting issue ? I'll investigate further, but if you can see an obvious solution that would be much appreciated.
Regards
Negger
 
I've changed the Age Control Format to General Number, and the Control now displays the correct age in Years. Can anyone tell me how to also display the Months & Days as well ?
I've tried editing the "yyyy" part of the code, but this generates an error message.
Thank you.
 
Try this out. On your form in the unbound box (in design view) open the properties and in the control source add this:

=Now()-[DOB]

Then format the unbound box to show yy

That would give you the result you want.
 
You will need to create a user defined function.

Code: 
Public Function CalcAge(dteDOB As Date, dteEnd As Date) As String
 
Dim intYears As Integer, intMonths As Integer, intDays As Integer
 
intMonths = DateDiff("m", dteDOB, dteEnd)
intDays = DateDiff("d", DateAdd("m", intMonths, dteDOB), dteEnd)
    If intDays < 0 Then
        intMonths = intMonths - 1
        intDays = DateDiff("d", DateAdd("m", intMonths, dteDOB), dteEnd)
    End If
 
intYears = intMonths \ 12
intMonths = intMonths Mod 12
 
CalcAge = intYears & " Years and " & intMonths & " Months And " & intDays & " Days"
 
End Function

Requires Testing.
 
Trevor G said:
Try this out. On your form in the unbound box (in design view) open the properties and in the control source add this:

=Now()-[DOB]

Then format the unbound box to show yy

That would give you the result you want.
Click to expand...

Even if you wanted to just calculate the age in years I think that you will find this is 1 year out on your birthday

Brian
 
=Now()-[DOB]

This will give the number of days.

To convert to Years you would first need to know how many leap years/days are required to be included in the conversion.

Also the OP wants Years Months and Days.

Could someone please explain precisely how & where I enter this code on the Form to get the Age field to automatically show the precise age (YYMM) or (YYMMDD) upon opening the Form e.g.
Click to expand...
 
RainLover said:
=Now()-[DOB]

This will give the number of days.



Also the OP wants Years Months and Days.
Click to expand...

I think that you will find that it gives a full date/time result, formatting it as yy will just return the short year, however as I said it fails at your birthday.
I only mentioned this incase another poster comes across the thread.

Brian
 
I think that you will find that it gives a full date/time result
Click to expand...

I placed it in a Query and it returns the Number of Days. No Formatting Applied.
 
Last edited:
Attached is a simple example.

This is untested except by me with Windows 7 and Access 2003.

Please let me know if there are any concerns.

EDIT

I should have tested more. The output for a Negative Result requires investigation.

For 1 Day negative it gives "0 Years and -1 Months And 30 Days"

Another STA to fix.
 

Attachments

Last edited:
Actually;
Now() - [DOB]
does return the number of days:-

Code: 
Sub Test()
    Dim DOB As Date
    
    DOB = #12:00:00 AM#                     [color=green]' < Midnight 30/12/1899[/color]

    MsgBox Now() - [DOB]                    [color=green]' < 41074.4570949074 (Days)[/color]

    MsgBox Format(Now() - [DOB], "yyyy")    [color=green]' < 2012 (Years)[/color]

End Sub

But the accurate calculation of age is ambiguous.
If, at any point in time, we assume that something has an accurate age we need more information to calculate it.

Remember the movie?

We need the time and place of calculation.
We need the time and place of birth.
We need to know on which side of the Greenwich meridian both places are.
We need to know if daylight saving was/is in place at both places.
We need to know if a date has been dropped from either place calendar over the period.
We need to know if the period spans epoch 0 (Midnight 30/12/1899).

Each of the above unknowns can cause an error with the day calculation.
If an error occurs with the day calculation then it can cause an error with the month calculation.
If an error occurs with the month calculation then it can cause an error with the year calculation.

A person may, in fact, be 25 years old but the calculation may show 24, 25 or 26 years old.

Now, horses are different, they only have one of two birthdays; 1/Jan and 1/Aug.
But it still depends on where they are born; northern or southern hemisphere.
And a horse may, in fact, be only one day old and still be a yearling.

So accurate age can be a complex calculation. :D

Chris.
 
Chris

Go and stand in the naughty corner for 15 Minutes.

That is 15 Minutes your time and location and no horse.

:rolleyes::rolleyes: :banghead::banghead:

The rest of the stuff I agree with. I have to because I know you ALWAYS TEST before posting. :cool::cool:
 
The rest of the stuff I agree with. I have to because I know you ALWAYS TEST before posting.
cool.gif
cool.gif
Click to expand...

Second thoughts.

The result is to be expected, but it is still not an accurate way to measure true age.

You need to test with a variety of dates.

The Leap Year will throw things out.

And don't forget the original question. The OP wants Years Months and Days.

EDIT

The result is in Days, not Years. Formatting does not help.

I would also use Date() not Now(). We are not drilling down to time.
 
Last edited:
If both data types are Date/Time then Leap Years are accounted for in the calculation.

Each offset is correct from Access epoch zero.
The difference between two Access epoch zero dates is calculated correctly and the calculated difference is in days.

Code: 
    MsgBox #3/1/2012# - #2/27/2012#     [color=green]' <  3   (Leap year).[/color]
    MsgBox #3/1/2011# - #2/27/2011#     [color=green]' <  2   (Non-leap year).[/color]

And now, back to horses…:D

Well, if we think about a one day old horse being a yearling then what is the age of a horse born on the day after the horse’s birthday?

Race steward; and what is the age of your horse?
Answer; don't know, it hasn't been born yet and I've been flogging a dead horse for 364 days? :D

Chris.
 
I should know better than to question you.

I will test later.

Going to Dentist soon.
 
Sorry to Negger for diverting a little.

ChrisO

What is wrong with the following.
Code: 
Sub Test2()
    Dim DOB As Date
 
' Your Numbers
    DOB = #12:00:00 AM#                     ' < Midnight 30/12/1899
    MsgBox Now() - [DOB]                    ' < 41074.4570949074 (Days)
    MsgBox Format(Now() - [DOB], "yyyy")    ' < 2012 (Years)
 
'My Numbers
    DOB = #6/14/2012#                       ' < Today's Date in Australia GMT + 10
    MsgBox Now() - [DOB]                    ' < I Get  0.69482638889167  (Days)
    MsgBox Format(Now() - [DOB], "yyyy")    ' < I get 1899 (Years) Should be 0.00
 
End Sub
 
Last edited:
I cannot retest now as I am on my iPad whilst having breakfast prior to spending the. Day out walking, but i'm sure when I did it in a query I got a full date ie 12345.6789 type answer which when formatted yy gave the correct years, except on a birthday.

Brian
 
Brianwarnock said:
I cannot retest now as I am on my iPad whilst having breakfast prior to spending the. Day out walking, but i'm sure when I did it in a query I got a full date ie 12345.6789 type answer which when formatted yy gave the correct years, except on a birthday.

Brian
Click to expand...

Just a little misunderstanding then. The result is a Decimal which can be converted/formatted to a year or part thereof.

EDIT

The result is still in DAYS, even though it is a Decimal.
A bit hard to convert to Years Months and Days.
 
Last edited:
You must log in or register to reply here.

Similar threads

J
Between/And
2
Replies
21
Views
664
Pat Hartman
P
S
Link records between two tables based on range?
Replies
3
Views
304
Mark_
M
L
Solved Problem calculating correct age from birth to death
Replies
11
Views
1,050
GPGeorge
GPGeorge
mloucel
Solved Creating a New Record issue.
Replies
14
Views
481
Pat Hartman
P
H
convert age to date of birth in three fields: year, month, and day.
Replies
3
Views
824
Pat Hartman
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom