How to calculate a Member's Age using a query

[mouk2007]

I have a table of the date of birth of my members in my DVD rental shop and I also have a table for the current date. I wish to calculate the age of my members. Could anyone please let me know the code that I must use in the query to do this.

Many Thanks

 

[lespaul]

why would you need a table for the current date? you can get that in a query:

SELECT BIRTH_DATE, (Now()-[BIRTH_DATE])/365.25 AS AGE FROM TableName

Leslie

Have you met Hardy Heron?

 

[mouk2007]

Thanks for the reply. I inserted the code you provided but I got a Syntax Error. Here is the SQL for my query:

SELECT Member.MemberNumber, Member.MemberDateOfBirth
FROM Member;

 

[lespaul]

that query gave you a Syntax error? Is MemberDateOfBirth really a Date/Time field?

SELECT MemberNumber, MemberDateOfBirth, (Now() - MemberDateOfBirth)/365.25 As Age
FROM Member;

Leslie

Have you met Hardy Heron?

 

[mouk2007]

GREAT! Thanks a lot. This code works however it shows the age with lots of decimal places, e.g 25.565434123. Could you please let me know how I could get rid of the decimal places.

 

[SoCalAccessPro]

Format((Now() - MemberDateOfBirth)/365.25),"Fixed") As Age


Tyrone Lumley
SoCalAccessPro

http://www.socalaccesspro.com

 

[TheAceMan1]

How are ya mouk2007 . . .

Be aware that the methods provided in this thread so far are inaccurate! Example:

Someone is born [blue]Dec 12, 1990[/blue]. On [blue]Jan 1, 2000[/blue] these methods will show an [blue]age of 10[/blue], when in fact (since Dec hasn't arrived yet) [purple]the true age is 9![/purple]

To circumvent this, is a select query that includes the BirthDay, add the following custom field ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]Age:DateDiff("yyyy", [[purple][B][I]BirthdayFieldName[/I][/B][/purple]], Now()) + Int(Format(Now(), "mmdd") < Format([[purple][B][I]BirthdayFieldName[/I][/B][/purple]], "mmdd"))[/blue]

Run the query to see the results.

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue] . . . Its [blue]worthy[/blue] reading!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[TheAceMan1]

Darn! Hit submitt too soon! . . .

With the code I provided, [blue]age changes on the exact birthdate![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886