Working out how old someone is

[colmoore]

Hi all,

I'm making a simple query to tell me how old a list of members are in a database.

I figure as I have their birthday, armed with todays date access should be able to tell me (in years only) how old they are.

Any help is appreciated

 

[The_Doc_Man]

You can use functions in queries. In Access Help, look up DateDiff and Date, two functions relevant to your request. How you use them is up to you.

 

[Mile-O]

Search for age calculations. There's numerous threads on this topic.

 

[raskew]

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

 

[colmoore]

Thank you

Thanks very much for this explanation. After I posted yesterday I did a search for this topic and found other methods for this computation. The one I stumbled across which seemed to be most popular was this:

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

What do you think of this formula? It seems to work ok

 

[Mile-O]

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

What do you think of this formula?

Use raskew's.

Int(), I believe, is a keyword just keept for backwards compatibility. As far as I know it has been superceded by the CInt() function.

 

[ghudson]

Calculate Age

Originally Posted by colmoore
DateDiff("yyyy",[Date of Birth],Date())+Int(Format(Date(),"mmdd")<Format([Date of Birth],"mmdd"))

What do you think of this formula?

Use raskew's.

Int(), I believe, is a keyword just keept for backwards compatibility. As far as I know it has been superceded by the CInt() function.

That formula is simular to the one Microsoft posted @ Calculating age from a birth date in Access and I think that one is more accurate based on the test I just ran with this...

Age: DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

for which I compared the results in Excel when I broke it down to the number of days and years between the dates. Here is the text from that link...

Calculating age from a birth date in Access Help
Assistance > Access 2003 > Expressions > Using Expressions

Applies to
Microsoft Office Access 2003
Microsoft Access 97, 2000, and 2002

In Access, you can calculate a person's age, in years, by using an expression. For example, you can enter a birth date in a control on a form and display the number of elapsed years from that date in another control. The following steps demonstrate this technique by creating a form, inserting two text boxes, and entering an expression.

In the Database window, click Forms under Objects, and then click New.
In the New Form dialog box, click Design View, and then click OK.
Using the Text Box tool in the toolbox, add two unbound text boxes to your form.
Set the Name property for one text box to Birthdate and the other to Age.
Set the Format property for the Birthdate text box to Short Date.
Set the ControlSource property for the Age text box to the following expression:
=DateDiff("yyyy", [Birthdate], Now())+ Int( Format(now(), "mmdd") < Format( [Birthdate], "mmdd") )

Switch to Form view.
In the Birthdate text box, type a date (mm/dd/yyyy), and then press the TAB key.
The value displayed in the Age text box is the number of elapsed years from the birth date.

Here is another interesting link from Microsoft... Using Expressions

 

[Brianwarnock]

When does Raskew's formula fail?
Why is Now() used when we are not interested in time?

Brian

 

[ghudson]

When does Raskew's formula fail?
Why is Now() used when we are not interested in time?

Brian

Raskew's formula incorrectly returned my wifes age.

Now() returns the date and time.

Try this if it makes you feel better.

Age2: DateDiff("yyyy",[DOB],Date())+Int(Format(Date(),"mmdd")<Format([DOB],"mmdd"))

 

[Brianwarnock]

Yes I know Now() returns date and time and it will work , it just seems bad practice to use it instead of Date() when the time is not required.
As to Raskews formula failing I have used it for years, atleast I think its the same I'd better check, and as I work in a chrity concerned with ages the correct calculation is important, I'd better do some retesting

Brian

 

[EMP]

Use raskew's.

Int(), I believe, is a keyword just keept for backwards compatibility. As far as I know it has been superceded by the CInt() function.

Int() and CInt() are two different types of functions. The former is a Math Function whereas the latter is a Type Conversion Function.

 

[ghudson]

Yes I know Now() returns date and time and it will work , it just seems bad practice to use it instead of Date() when the time is not required.

Remember that the formula came from Microsoft so you had better be careful with that tone!

Do what I did for a test. Use Excel to test a few various dates to compare the number of days and the years between their DOB and today. The date that tripped on the other formula for the calculated number of years was 03/02/1967. Microsoft's formula got it right.

 

[raskew]

Raskew's formula incorrectly returned my wifes age.

I'm curious about that. Could you provide a sample set of dates where the formula returned an incorrect age?

Best wishes - Bob

 

[EMP]

The two Age expressions should produce the same result.


DOB = 03/02/1967
Date() = 05/12/2005 (May 12, 2005)

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

Age = DateDiff("yyyy",DOB,Date()) + Int(Format(Date(),"mmdd") < Format(DOB,"mmdd"))


(Date() < DateSerial(Year(Date()),Month(DOB),Day(DOB)))
= 05/12/2005 < 03/02/2005
= False
= 0

Int(Format(Date(),"mmdd") < Format(DOB,"mmdd"))
= Int(Format(05/12/2005,"mmdd") < Format(03/02/1967,"mmdd"))
= Int("0512" < "0302")
= Int(False)
= Int(0)
= 0

Hence, the two age expressions are each reduced to
DateDiff("yyyy",DOB,Date()) + 0


Basically, both < comparisons are comparing the "day and month" assuming the "year" is the same.

From the above, we also see that we can even remove the Int and just leave the < comparison inside a pair of brackets, i.e.
DateDiff("yyyy",DOB,Date()) + (Format(Date(),"mmdd") < Format(DOB,"mmdd"))

-

 

[KenHigg]

Working out how old someone is

Subtract the year the person was born in from the current date and add 86. Then simply subtract Col's age and, wah-la, you have their age.

 

[Lister]

Try here

 

[KenHigg]

Wonder how we can get oldsoftboss and orbic1 to start a thread?

 

[raskew]

Ken -

Where does 86 come in. Please provide an example.

Bob

 

[KenHigg]

Ken -

Where does 86 come in. Please provide an example.

Bob

Sorry, It was my feeble attemp at humor. Read it through a couple more time and maybe it'll fall in place...

 

[raskew]

ghudson: please show the proof

You made the claim that the formula returned inaccurate results. You may be absolutely correct, or not. Please provide the dates you used to test the formula.

Bob