Figure out End of year age based on a date (1 Viewer)

rinova · May 15, 2014

Hello everyone,

I need some help with an age query.

Here is the situation:
I have a date of service [DOS] and a date of birth [Birth Date]
I'm trying to calculate the age at the last day of the date of service year not the current year.

Example:
[Birth Date] = 6/25/1993
[DOS] = 10/18/2013
Age at the last day of the date of service year (12/31/2013) = 20

Having a hard time figuring out how to do this. I tried

Code:

DateDiff("yyyy",[BIRTH DATE],[DOS])+Int(Format([DOS],"mmdd")<Format([BIRTH DATE],"mmdd"))

but this does not work correctly.

Any help would be greatly appreciated.

Thank you,
Rich

KenHigg · May 15, 2014

What is the format and int stuff supposed to do?

namliam · May 15, 2014

how about moving DOS to the 'required date' ??

DateDiff("yyyy",[BIRTH DATE],dateserial(year([DOS]),12,1) )

rinova · May 15, 2014

KenHigg said:
What is the format and int stuff supposed to do?

The Int function returns the integer portion of a number. (Rounding numbers). I used it in another query and thought it would work but it didn't.

The code namliam provided proved that I didn't need to use Int or Format functions. I tend to make things harder then they need to be. LOL!

Thanks for the help!

Keith Tedbury · May 15, 2014

Problem with using Datediff is it will only compare the year. So if you had a 12/31/2000 and the 01/01/2001 it would see the the year difference as 1.

The code below should give you their age at the DOS date

Code:

Format([DOS] + 1 - [Birth Date], "yy")

Take away the birth date from the DOS and then format it as years. You need to add 1 to the DOS otherwise it would add a year the day after the birthday instead of on it.

rinova · May 15, 2014

I used this code to calculate DOS age and it works as well.

Code:

Age at DOS: DateDiff("yyyy",[TestDOB],[DOS])+Int(Format([DOS],"mmdd")<Format([TestDOB],"mmdd"))

I see that your code works and is not as long as mine (Which I like) and it displays the age as "00"

If my code is not correct or if I'm missing something please let me know, I'm new to MS Access and SQL.

Thanks Keith

-Rich

namliam · May 16, 2014

Keith Tedbury said:
Problem with using Datediff is it will only compare the year.

Correct however if you are comparing to the "End of the year" that is not a problem at all

@ Ken

The int and format stuff takes the year difference and checks if the month of the DOB is already passed or not....
Basicaly returning a True or a False, which in access is -1 and 0.
You dont really need the INT part, it would work without it just as well assuming access doesnt screw you over using the implicit conversion.
The INT function basicaly converts the TRUE and FALSE explicitly to -1 and 0 to ensure access actually uses the NUMBER and not the displayed value

Keith Tedbury · May 16, 2014

Sorry your absolutely right. I miss read the question and thought he was looking for the age at the DOS date which is what my code does and not the end of year of for the DOS.

rinova · May 16, 2014

I just wanted to say thanks to everyone for helping me.

-Rich

Figure out End of year age based on a date (1 Viewer)

rinova

Registered User.

KenHigg

Registered User

namliam

The Mailman - AWF VIP

rinova

Registered User.

Keith Tedbury

Registered User.

rinova

Registered User.

namliam

The Mailman - AWF VIP

Keith Tedbury

Registered User.

rinova

Registered User.

Similar threads

Users who are viewing this thread