Determine Age

P

PaulS44

New member

Local time

Today, 02:54

Joined

Nov 8, 2005

Messages

5

• Nov 9, 2005

• #1

Can someone help me in regard to setting up an age field. For example: say a person was born on August 9, 1944 and I want to find out how old that person is on a continuing yearly basis. So I set up the DOB field and Age field. What is the formula to determine the age.?

Thank you

 

KenHigg

Registered User

Local time

Today, 05:54

Joined

Jun 9, 2004

Messages

13,291

• Nov 9, 2005

• #2

datediff("yyyy", date(),[myDOB])

???

 

B

Bat17

Registered User.

Local time

Today, 10:54

Joined

Sep 24, 2004

Messages

1,687

• Nov 9, 2005

• #3

take your pick

Code:

Function strAge(date1 As Date, date2 As Date) As String
    Dim y As Integer
    Dim M As Integer
    Dim d As Integer
    Dim Temp1 As Date
    Temp1 = DateSerial(Year(date2), Month(date1), Day(date1))
    y = Year(date2) - Year(date1) + (Temp1 > date2)
    M = Month(date2) - Month(date1) - (12 * (Temp1 > date2))
    d = Day(date2) - Day(date1)
    If d < 0 Then
        M = M - 1
        d = Day(DateSerial(Year(date2), Month(date2), 0)) + d
    End If
    strAge = y & " years " & M & " months " & d & " days"
End Function

Function Age(date1 As Date, date2 As Date) As Integer
    Age = Year(date2) - Year(date1) + (DateSerial(Year(date2), Month(date1), Day(date1)) > date2)
End Function

Peter

 

R

raskew

AWF VIP

Local time

Today, 04:54

Joined

Jun 2, 2001

Messages

2,731

• Nov 9, 2005

• #4

It may seem that the DateDiff() function should return Age based on Date of Birth (DOB) and a user-specified date. However, there's a problem with that solution since the DateDiff("yyyy"....) merely subtracts one year from another.

To accurately return age in years,we must know whether the month/day portion of the target date is >= than that of the DOB. We can add a boolean statement that examines month/days of the two dates and returns -1 (false) or 0 (true) to indicate if the month/day of the target date meets or exceeds that of the DOB. Here's the expression:

Age = DateDiff("yyyy", DOB, Target) + (Target < DateSerial(Year(Target), Month(DOB), Day(DOB)))

To use, replace DOB and Target with your field names.

HTH - Bob

 

P

PaulS44

New member

Local time

Today, 02:54

Joined

Nov 8, 2005

Messages

5

• Nov 10, 2005

• #5

Date of Birth

Would someone be kind enought to type in the formula for these two fields to determine age of a person.
DOB -- Stands for Date of Birth i.e. (day, month, year)
Age -- Age of the person now

I very new at all of this.
I am from the US.

Thanks for any help you can give me

 

R

raskew

AWF VIP

Local time

Today, 04:54

Joined

Jun 2, 2001

Messages

2,731

• Nov 10, 2005

• #6

Paul -

Here's a sample query based on Northwind's Employees table.
The query-SQL has been formatted to make it easier to read. If you copy / paste it to a new query, it will revert to the normal query-SQL format.

Code:

SELECT
    Employees.LastName
  , Employees.FirstName
  , Employees.BirthDate AS DOB
  , DateDiff("yyyy",[DOB],Date())+(Date()<DateSerial(Year(Date()),Month([DOB]),Day([DOB]))) AS Age
FROM
   Employees;

HTH - Bob

 

Last edited: Nov 10, 2005

ghudson

Registered User.

Local time

Today, 05:54

Joined

Jun 8, 2002

Messages

6,193

• Nov 10, 2005

• #7

PaulS44,

How many times are your going to post your question about calculating a persons age? Double posting is frowned upon yet your triple posting only compounds this issue. Please stop posting new threads related to the same subject!

http://www.access-programmers.co.uk/forums/showthread.php?t=96987
http://www.access-programmers.co.uk/forums/showthread.php?t=96991
http://www.access-programmers.co.uk/forums/showthread.php?t=97059
http://www.access-programmers.co.uk/forums/showthread.php?t=97056

Mods, can you merge this mess into one post?

 

Last edited: Nov 10, 2005

R

raskew

AWF VIP

Local time

Today, 04:54

Joined

Jun 2, 2001

Messages

2,731

• Nov 10, 2005

• #8

Holy Shizznik, Batman,

Not to mention PM's requesting assistance.

Geez,

Bob

 

B

Bat17

Registered User.

Local time

Today, 10:54

Joined

Sep 24, 2004

Messages

1,687

• Nov 10, 2005

• #9

Take the code I posted and save it into a new module and save the module as modAge.

in a Age field of the form put
=age([dob],date())
If you want the years, months, days, use
=strAge([dob],date())

Another point, Age is calculated so the is no need to save it so the field should not be bound to a table.

You will find that you get on far better in the forum if you stick to your original post and ask for clarification if you dont understand how to use an answer that is given.

Peter