By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,416 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

How do you calculate someones age?

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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******@pcdatasheet.com
www.pcdatasheet.com
"Jan Szymczuk" <sz******@btinternet.com> wrote in message
news:cq**********@sparta.btinternet.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

P: n/a
=DateDiff("yyyy", Date(), [DoB]) - _
IIf(Format(Date(), "mmdd") < Format([DoB], "mmdd", 1, 0)

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Jan Szymczuk" <sz******@btinternet.com> wrote in message
news:cq**********@sparta.btinternet.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

P: n/a
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 "BeforeUpdate". 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******@btinternet.com> wrote in message
news:cq**********@sparta.btinternet.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

P: n/a
Thankyou Douglas for your simple and very elegant solution, I shall forever
be in your debt.

Jan
"Jan Szymczuk" <sz******@btinternet.com> wrote in message
news:cq**********@sparta.btinternet.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 discussion thread is closed

Replies have been disabled for this discussion.