How old R - U - Now

M

Mr Hat

I have a field on my form for Birth Day and one for age. I want the persons
Age field to auto update, so that if it is the day after that persons birth
day their age will change to the persons proper age. I know the code goes
under Control Source for the text box but I don't know what to write there.

Any suggestions
 
D

Dirk Goldgar

Mr Hat said:
I have a field on my form for Birth Day and one for age. I want the
persons Age field to auto update, so that if it is the day after that
persons birth day their age will change to the persons proper age. I
know the code goes under Control Source for the text box but I don't
know what to write there.

Any suggestions
Click to expand...

There are several expressions and functions for calculating age from
birth date posted on this page at the Access Web:

http://www.mvps.org/access/datetime/date0001.htm

You could adapt the first one to your needs. Assuming that you have a
date field named "Birth Day" (which must actually hold the date of
birth, not just the month and day of the month), then your Age text box
could have this Control Source:

=DateDiff("yyyy", [Birth Day], Date()) + Int(Format(Date()), "mmdd")
< Format( [Birth Day], "mmdd"))

Note: the above expression will have been broken onto multiple lines by
the newsreader, but it was entered on one line, and should be all on one
line in the Control Source.
 
P

Paul Overway

You'll need a function like this:

Function GetAge(BirthDate As Date, Optional AsOfDate As Date = 0) As Integer

On Error Resume Next

'See if an arg was passed for end date, if not, use today's date
If AsOfDate = 0 Then
AsOfDate = Date
End If

'Get the number of years difference between the birthdate and the
enddate
GetAge = DateDiff("yyyy", BirthDate, AsOfDate)

'If the birthdate is not passed the enddate in the calendar, we need to
subtract a year
GetAge = GetAge + (AsOfDate < DateSerial(Year(AsOfDate),
Month(BirthDate), Day(BirthDate)))

End Function

The set the controlsource for the age field to
=IIF(IsNull([YourBirthDayField]),Null,GetAge([YourBirthdayField]))
 
D

Dirk Goldgar

Dirk Goldgar said:
=DateDiff("yyyy", [Birth Day], Date()) + Int(Format(Date()),
"mmdd") < Format( [Birth Day], "mmdd"))
Click to expand...

Oops, looks like I stuck in an extra parenthesis: Try this:

=DateDiff("yyyy", [Birth Day], Date()) + Int(Format(Date(), "mmdd")
< Format([Birth Day], "mmdd"))
 
Top