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
