Date of Birth

[Stewart Mashiter]

Can anyone help me with a method for working out someones
age from their date of birth in a form?

All help appreciated.

Thanks.

 

[Ed Warren]

You have to use VBA code to get a function that actually works.

I copied the code below from Microsoft hope it helps.

Ed Warren

--------------------------------------------------------------code
Calculating Age

Calculating someones age, given that persons birth date, is a commonplace
need in data manipulation. Unfortuntaely, VBA doesnt give a complete and
correct method for calculating a persons age.

You might be tempted to use this formula:


Age = DateDiff("yyyy", Birthdate, Date)

to calculate age, but this doesnt quite work. If the birth date hasnt yet
occurred this year, the Age value will be off by 1. For example, imagine
your birthday is December 31, and you were born in 1950. If today is October
1, 1997, subtracting the year portions of the two dates (1997 1950) would
indicate that you were 47 years old. In reality, by the standard way of
figuring such things, youre still only 46.

To handle this discrepancy, the dhAge function in Listing 2.22 not only
subtracts one Year portion of the dates from the other, it checks whether
the birth date has already occurred this year. If it hasnt, the function
subtracts 1 from the calculation, returning the correct age.

In addition, dhAge allows you to pass an optional second date: the date on
which to calculate the age. If you pass nothing for the second parameter,
the code assumes you want to use the current date as the ending date. That
is, if you use a call like this:


intAge = dhAge(#5/22/59#)

youll find the current age of someone born on May 22, 1959. If you call the
function like this:

intAge = dhAge(#5/22/59#, #1/1/2000#)

youll find out how old the same person will be on the first day of 2000.

Listing 2.22: One Solution for Calculating Age


Function dhAge(dtmBD As Date, Optional dtmDate As Date = 0) _

As Integer

' This procedure is stored as dhAgeUnused in the sample

' module.

Dim intAge As Integer

If dtmDate = 0 Then

' Did the caller pass in a date? If not, use

' the current date.

dtmDate = Date

End If

intAge = DateDiff("yyyy", dtmBD, dtmDate)

If dtmDate < DateSerial(Year(dtmDate), Month(dtmBD), _

Day(dtmBD)) Then

intAge = intAge - 1

End If

dhAge = intAge

End Function

You might also be tempted to solve this problem by dividing the difference
between the two dates, in days, by 365.25. This works for some combinations
of dates, but not for all. Its just not worth the margin of error. The
functions presented here are simple enough that theyre a reasonable
replacement for the simple division that otherwise seems intuitive.

If youre looking for the smallest possible solution, perhaps at the expense
of readability, you could use the version in Listing 2.23 instead. It relies
on the fact that a true expression is equal to the value 1 and a false
expression is equal to 0. The function adds 1 or 0 to the year difference,
depending on whether the specified birth date has passed.

-----------------------------------------------------------end code

 

[Dave B]

Hi,

The easiest way of doing it is to create a query and by subtracting the date
of NOW from the date u entered will give u the amount of days elapsed
between the two dates. However after u have done this u must make sure that
u have INT typed in before the calculation in the query so that it rounds
the number to the nearest integer. Also make sure that the FORMAT setting
within properties is set to FIXED within the query field.

If u just enter the below query without any of the above it will give u an
age that is 1 year older than it shud be.

AGE: Int(Now()-[DATE YOU HAVE ENTERED]) this will give u the amount of days
elapsed

Dave

 

[Dave B]

forgot to mention u of course need to divide it by 365.25 to get the amount
of years from the days

..25 is to take into account of leap years

Dave

Hi,

The easiest way of doing it is to create a query and by subtracting the date
of NOW from the date u entered will give u the amount of days elapsed
between the two dates. However after u have done this u must make sure that
u have INT typed in before the calculation in the query so that it rounds
the number to the nearest integer. Also make sure that the FORMAT setting
within properties is set to FIXED within the query field.

If u just enter the below query without any of the above it will give u an
age that is 1 year older than it shud be.

AGE: Int(Now()-[DATE YOU HAVE ENTERED]) this will give u the amount of days
elapsed

Dave

Can anyone help me with a method for working out someones
age from their date of birth in a form?

All help appreciated.

Thanks.

 

[jwm]

Assuming the field in which the birth date is stored is named "dtmBirthDate"
and is available to the form/report, the following placed in an unbound
control will work:

=DateDiff("yyyy",[dtmBirthDate],Date())+(DateSerial(Year(Date()),Month([dtmB
irthDate]),Day([dtmBirthDate]))>=Date())

Change the field name to yours.