Need to have a query that changes Date of Birth to Age 2

[CrimsonLily]

Ok, I've got a database that lists people by date of birth. And for a project that I'm doing I need the actual age in years.

Is there a query that will calculate a date of birth into an age in years??

Please help, I don't even know where to begin.

Thanks!!!

CrimsonLily

 

[MattJW]

You can use the DateDiff and Now Functions within your query to calculate age. The Now() function basically returns the date and time from your System Clock. The DateDiff function calculates the difference between 2 dates for a given time interval (eg. years). You will therefore need to add a field to your query along the following lines..


Age: DateDiff("y",Now(),DateOfBirth)

Hope this helps, let me know if you need further instruction!

 

[r937]

that's close but you have to make an adjustment

if my birthday is 1981-04-29 and today is 2003-04-15 then 2003-1981=22 but i have not yet reached my 22nd birthday

here's the way i do it, feel free to copy:

[tt]select date() as today
, thedate as birthdate
, year(date()) - year(thedate)
- iif(month(date())>month(thedate),0,
iif(month(date())<month(thedate),1,
iif(day(date())<day(thedate),1,0)))
as age
from yourtable[/tt]

rudy

http://r937.com/

 

[CrimsonLily]

This is great.
One more question: What would I need to do to this code so that it doesn't prompt me for a date. I just want it to figure all of the ages for all of the people in a column.

Thanks so much for your help!! I've never had a response so quickly before

CrimsonLily

 

[CrimsonLily]

r937: do I add this code into Modules?

 

[r937]

no, that's a query, and it shouldn't prompt for anything

it compares date() i.e. today with the date of birth in column thedate -- change this to your column name

paste it into the SQL view of the Query tab

rudy

 

[CrimsonLily]

I don't know a whole lot about Access so let me double check this code with you

OK, so if my birth date column in my table is called DOB & my table is called People

SELECT date(04/15/03)
, thedate as birthdate
, year(date()) - year(thedate)
- iif(month(date())>month(DOB),0,
iif(month(date())<month(DOB),1,
iif(day(date())<day(DOB),1,0)))
as age
FROM People

 

[r937]

not quite ;o)

[tt]SELECT date() as today
, DOB as birthdate
, year(date()) - year(DOB)
- iif(month(date())>month(DOB),0,
iif(month(date())<month(DOB),1,
iif(day(date())<day(DOB),1,0)))
as age
FROM People[/tt]

rudy

 

[CrimsonLily]

That is so AWESOME!! THANKS so much!! I can be a bit dense sometimes. I appreciate your patience and your help greatly!!!

Thanks Again

CrimsonLily

 

[CrimsonLily]

Ok now I have a question about the results. A couple of the ages are showing up as negative numbers. Is there something I can do to remedy this?

Thanks so much

 

[r937]

what are the birthdates for the negative ages?

 

[CrimsonLily]

They are 4/8/27 & 9/22/29

 

[r937]

4/8/27 is probably being interpreted as 2004-08-27 which gives age= -2

9/22/29 is 1929-09-22 which gives age= 73

what is the datatype of the DOB field?

 

[CrimsonLily]

The negative number that it's giving me are:

9/22/29 -27
4/8/27 -24

From what I've figured those number are exactly what the actual age is minus 100.

 

[r937]

weird

instead of

SELECT date() as today
, DOB as birthdate

try

SELECT date() as today
, format(DOB,&quot;yyyy mm dd&quot as birthdate

and see what that prints

 

[CrimsonLily]

The data type of the DOB field is date/time(short date). Would I be better off changing it to longer date?

I changed the code and it still gives me those 2 negative numbers. It just doesn't make any sense.

They are both under 30, and so are days of the month. Could it somehow be confusing the year with the day of the month?

I just have to tell you how much I appreciate all your help. I'm sure when you answered this post you didn't expect to be answering questions for hours and hours

Thanks again

 

[coco86]

Check your regional settings for date (in the control panel). You probably have short date set to mm/dd/yy and 2 digit years being interpreted between 1930 and 2029. Therefore 1927 and 1929 are being interpreted as 2027 and 2029.

-Coco

 

[coco86]

PS, you could fix this problem by changing your query to something like

SELECT date() as today
, DOB as birthdate
, year(date()) - year(DOB)
- iif(month(date())>month(DOB),0,
iif(month(date())<month(DOB),1,
iif(day(date())<day(DOB),1,0))) +
iif(year(date()) - year(DOB) < 0,100,0) as age
FROM People


-Coco

 

[CrimsonLily]

Thanks for all the help! I really appreicate it. What should I change my regional settings to?

Thanks for the addition to the code, I believe it did fix the problem.

 

[coco86]

If you want to change your regional settings, mm/dd/yyyy would circumvent the problem. However, I don't think it will do any good if your data is already stored in mm/dd/yyyy format. And changing the date range for interpretation just shifts the years with the problems. (although in this particular instance, changing the range to 1925-2024 would have put all of your dates inside the range. However, all other applications would be affected by the change as well.)

I think modifying the SQL statement was the best option. Looking at the regional setting just illustrates why you were getting the results you were getting.

-Coco