473,545 Members | 1,821 Online

How do you calculate someones age?

I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age: (this needs to be automated to show years e.g. 46)

I am trying to get the age to be automatically shown after a persons DoB
(date of birth) is entered into the DoB field. I have tried using this
formula in the Control Source of the Age field =DAYS360([DoB],Now()) and
setting the format to yy, but am getting totally confused. Can anyone help
me (I know very little Visual Basic and hoping to get arounfd this problem
using some functiomn of formala to calculate the age)

Jan

Nov 13 '05 #1
4 9323
Look back at recent posts in the newsgroup, someone asked this same question
recently.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdata sheet.com
www.pcdatasheet.com
"Jan Szymczuk" <sz******@btint ernet.com> wrote in message
news:cq******** **@sparta.btint ernet.com...
I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age: (this needs to be automated to show years e.g. 46)

I am trying to get the age to be automatically shown after a persons DoB
(date of birth) is entered into the DoB field. I have tried using this
formula in the Control Source of the Age field =DAYS360([DoB],Now()) and
setting the format to yy, but am getting totally confused. Can anyone help
me (I know very little Visual Basic and hoping to get arounfd this problem
using some functiomn of formala to calculate the age)

Jan

Nov 13 '05 #2
=DateDiff("yyyy ", Date(), [DoB]) - _
IIf(Format(Date (), "mmdd") < Format([DoB], "mmdd", 1, 0)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"Jan Szymczuk" <sz******@btint ernet.com> wrote in message
news:cq******** **@sparta.btint ernet.com...
I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age: (this needs to be automated to show years e.g. 46)

I am trying to get the age to be automatically shown after a persons DoB
(date of birth) is entered into the DoB field. I have tried using this
formula in the Control Source of the Age field =DAYS360([DoB],Now()) and
setting the format to yy, but am getting totally confused. Can anyone help
me (I know very little Visual Basic and hoping to get arounfd this problem
using some functiomn of formala to calculate the age)

Jan

Nov 13 '05 #3
Hi Jan:

At http://members.iinet.net.au/~allenbrowne/func-08.html, you will find the
following function by Allen Browne:

'-----------------------------------------------------------------------------
Function Age(varDOB As Variant, Optional varAsOf As Variant) As Variant
'Purpose: Return the Age in years.
'Arguments: varDOB = Date Of Birth
' varAsOf = the date to calculate the age at, or today if
missing.
'Return: Whole number of years.
Dim dtDOB As Date
Dim dtAsOf As Date
Dim dtBDay As Date 'Birthday in the year of calculation.

Age = Null 'Initialize to Null

'Validate parameters
If IsDate(varDOB) Then
dtDOB = varDOB

If Not IsDate(varAsOf) Then 'Date to calculate age from.
dtAsOf = Date
Else
dtAsOf = varAsOf
End If

If dtAsOf >= dtDOB Then 'Calculate only if it's after person was
born.
dtBDay = DateSerial(Year (dtAsOf), Month(dtDOB), Day(dtDOB))
Age = DateDiff("yyyy" , dtDOB, dtAsOf) + (dtBDay > dtAsOf)
End If

End If
End Function
'-----------------------------------------------------------------------------

I assume you have a textbox for DOB input, maybe named txtDOB and a textbox
to show Age, maybe named txtAge. In Form Design view, click on the txtDOB,
and then right-click the mouse. At the bottom of the window that opens up,
choose "Properties ". When the Properties window opens up, choose the
"Event" tab. The first event in the listing is "BeforeUpda te". Click on it,
and then on the elipsis ("...")that appears. When the Choose Builder
window appears, choose "Code Builder". You will be taken to the Visual
Basic Editor, and be inside an empty subroutine for the BeforeUpdate event.
Type:

Me!txtAge = Age(Me!txtDOB)

Then, paste the Age function down below your BeforeUpdate soubroutine.

If everything works correctly, when you enter a Date of Birth and then leave
the txtDOB textbox, the person's age will appear in txtAge. Change the
textbox names if necessary to what you have. I hope it works for you.

Alan

"Jan Szymczuk" <sz******@btint ernet.com> wrote in message
news:cq******** **@sparta.btint ernet.com...
I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age: (this needs to be automated to show years e.g. 46)

I am trying to get the age to be automatically shown after a persons DoB
(date of birth) is entered into the DoB field. I have tried using this
formula in the Control Source of the Age field =DAYS360([DoB],Now()) and
setting the format to yy, but am getting totally confused. Can anyone help
me (I know very little Visual Basic and hoping to get arounfd this problem
using some functiomn of formala to calculate the age)

Jan

Nov 13 '05 #4
Thankyou Douglas for your simple and very elegant solution, I shall forever

Jan
"Jan Szymczuk" <sz******@btint ernet.com> wrote in message
news:cq******** **@sparta.btint ernet.com...
I'm creating an MS Access 2000 database where I have a number of people
entered using simple basic fields, Surname: SMITH
Forenames: John
DoB: 09/09/1958
Age: (this needs to be automated to show years e.g. 46)

I am trying to get the age to be automatically shown after a persons DoB
(date of birth) is entered into the DoB field. I have tried using this
formula in the Control Source of the Age field =DAYS360([DoB],Now()) and
setting the format to yy, but am getting totally confused. Can anyone help
me (I know very little Visual Basic and hoping to get arounfd this problem
using some functiomn of formala to calculate the age)

Jan

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.