Calculating an age from a date of birth field

[funkmonsteruk]

I'm currently creating a query based on some data which includes a date of birth field.

I need to calculate someones current age in years and then run a crosstab to show me data based on age groups (eg.<40 years, between 40 and 50 years, etc)

I am unable to do either of these tasks, can anybody help?

 

[funkmonsteruk]

Sorry forgot to mention this is all being done in access '97

 

[MichaelRed]

Public Function basAge(BirthDay As Date) As Long

    'Michael Red 8/8/02     Simplistic Age Cals

    basAge = DateDiff("yyyy", BirthDay, Date)
    basAge = basAge + (DateSerial(Year(Date), Month(BirthDay), Day(BirthDay)) > Date)

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[funkmonsteruk]

Any ideas how i would accomplish this with an access query - not using vba?

 

[SunGodly]

Datediff function should work in a query as well.

 

[AWithers]

something like this

SELECT DateDiff(&quot;yyyy&quot;,yourdatefield,Date())+(DateSerial(Year(Date()),Month(yourdatefield),Day(yourdatefield))>Date()) AS Age
FROM yourtable;

or add MichaelReds function to a module and call

select basage(yourbirthdayfield)from yourtable


Andy

 

[KenReay]

Hi

You can get an approximate age with datediff(), but the routine posted by Michael gives a more accurate result.

So in brief if you do not want to using any VBA then you will have to settle for the approximate answer.

Either way, to incorpartae into a query, make a calcualted column so:

AgeateDiff(&quot;yyyy&quot;,[DOB],Date())

or using Michaels routine

Age:basAge([DOB]) - assuming you have paseted Michaels code into a module Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[scriverb]

The following can be pasted into a column of a query and will return the accurate age based on a BIRTH_DATE field and the system date. It takes into consideration leap year for the most part as well as the DOB being the same day as the system date.

Adjustment for Birthdays: DOB's that happen to be birthdays when compared to todays date need to be handled slightly differently or they will be off by one day and will not rollover to the next age until the next day. The following adjustment handles even those situations.

Age: IIf(DatePart(&quot;m&quot;,[tblYourTable]![BIRTH_DATE])=DatePart(&quot;m&quot;,Date()) And DatePart(&quot;d&quot;,[tblYourTable]![BIRTH_DATE])=DatePart(&quot;d&quot;,Date()), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)), CInt((DateDiff('d',[tblYourTable]![BIRTH_DATE],Date())/365.25)-0.5))

The IIF compares the month and day of the date of birth field to system date month and day. If the result is true it means that this is the birthdate of the individual and the subtraction of the .5 is not necessary. Otherwise, the calculations require the subtraction of .5 before rounding.


Bob Scriver

 

[funkmonsteruk]

Cheers guys - problem solved - have also posted a query on grouping ages, don't know if you guys could help with that as well?

 

[MichaelRed]

to a LIMITED extent, see &quot;Partition&quot; in help.


MichaelRed
m.red@att.net

Searching for employment in all the wrong places