Age query

[Hayley Baxter]

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

 

[Graham T]

Hayley

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

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

HTH

 

[ColinEssex]

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).]

 

[raskew]

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

 

[ColinEssex]

Hi

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

It seemed quite accurate, and it works !!

Col

 

[Hayley Baxter]

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

Cheers

 

[Hayley Baxter]

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?

 

[ColinEssex]

Well done Hayley !!!

You get todays star prize

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

Col

 

[ColinEssex]

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

 

[Hayley Baxter]

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

 

[Rich]

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

 

[simongallop]

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.

 

[dataadept]

Please Help Me Experts

Date1: 22/06/2014
Date2: 12/01/2019
Answer= 4 Years 6 Months 19 Days
it is possible in Access Query

excel formula =DATEDIF(A2,C2,"y")&" Years "&DATEDIF(A2,C2,"ym")&" Months "&DATEDIF(A2,C2,"md")&" Days "

i need it in access query

Thanks in advance

 

[Gasman]

Access uses the DateDiff function

https://support.office.com/en-us/article/datediff-function-e6dd7ee6-3d01-4531-905c-e24fc238f85f