How can I list a persons age on the date they were admitted into hospital. I have the fields DOB and DateAdmitted. Do I need another field called Age so that I can return the age as a number ie 18 or can Ido this without this field?

Can anyone help with the criteria for this?
Many thanks

Hayley

You can add an expression to the query, form or report to calculate the Age:

Age At Admittence: DateDiff("yyyy",[DOB],[DateAdmitted])

HTH

Hayley

Another way is to use

AgeInYears:Int((DateDiff("s",[DOB],[AdmitDate])/31556952))

Col


[This message has been edited by ColinEssex (edited 04-17-2002).]

Col-

Your 31556952 seconds equates to 365.2425 days in a year. Would you explain the significance of that versus 365.25 days (which I believed,up until now (maybe) was the standard for calculating days in a year).

Thanks,

Bob

Hi

I got that calculation from this site
http://www.rogersaccesslibrary.com

It seemed quite accurate, and it works !!

Col

Thanks to everyone who responded, I know have this working.

Cheers

Ok I do have a slight problem with this. Colin I used your method which almost works perfectly, however I have a scenario where I have entered some dummy data

these are the dates i used

example 1
Dob
01/01/00

Date Admitted
01/01/02

The person is 2 (admitted on their birthday - this works

BUT example 2
Dob
17/04/00

DateAdmitted
17/04/02

So like in case 1 this person should be 2 on their birthday as well, however the query tells me they are 1 but yet my first example works!

Any ideas?

Well done Hayley !!!

You get todays star prize

Haven't a clue yet - working on it. Isn't it odd?

Col

Hi Hayley

I'm sorry, I just can't fathom this one out.
Why it should be ok on 01/01/00 and 01/01/02 and not others seems a mystery. Perhaps it's something to do with a hidden time or something.Maybe it's the number of seconds wrong.
Maybe someone can help out here please.
Col

Well it's a strange one Col but finding a solution probably matters alot more to you than me since I can enter dummy data and just use the ones that work because I am only helping out my friend with her db but when using a real db there's just no getting away with it. I'll play around with it a little and let you know if I have any luck

Hayley

Function Age(dteA As Date, dteB As Date) As Integer
If Day(dteA) > Day(dteB) Then
Age = Int((DateDiff("m", dteA, dteB) - 1) / 12)
Else
Age = Int((DateDiff("m", dteA, dteB)) / 12)
End If
End Function

Reason for it working over Jan but not April is 'cause Feb 29th 2000 brings back the .25 day per year overshoot that the formula creates.