Age query (1 Viewer)

Hayley Baxter · Apr 17, 2002

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 · Apr 17, 2002

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 · Apr 17, 2002

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 · Apr 18, 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

ColinEssex · Apr 18, 2002

Hi

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

It seemed quite accurate, and it works !!

Col

Hayley Baxter · Apr 18, 2002

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

Cheers

Hayley Baxter · Apr 18, 2002

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 · Apr 18, 2002

Well done Hayley !!!

You get todays star prize

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

Col

ColinEssex · Apr 18, 2002

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 · Apr 18, 2002

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 · Apr 18, 2002

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 · Apr 18, 2002

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 · Feb 19, 2019

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 · Feb 19, 2019

Access uses the DateDiff function

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

Age query (1 Viewer)

Hayley Baxter

Registered User.

Graham T

Registered User.

ColinEssex

Old registered user

raskew

AWF VIP

ColinEssex

Old registered user

Hayley Baxter

Registered User.

Hayley Baxter

Registered User.

ColinEssex

Old registered user

ColinEssex

Old registered user

Hayley Baxter

Registered User.

Rich

Guest

simongallop

Registered User.

dataadept

New member

Gasman

Enthusiastic Amateur

Similar threads

Users who are viewing this thread