×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Calculating Age for future dates

Calculating Age for future dates

Calculating Age for future dates

(OP)

Hi

I have a list of children that may appear in different financial years and I need to work out their age based off this,
in the example below Child1 was born on 12 Feb 2008 and appears in 3 financial years.

Is there a way to calculate his next birthday based off Calc_Year and then therefore be able to calculate his age.

fin_year and calc_year are automatically pulled through in the query as the user selects a term on the form, i.e "spring 2011"
which on the table is associated with fin_year and calc_year

The only but I'm struggling with is the "next birthday in calc year".

Michael



CODE --> datasheet

Childsname	DOB	        fin_Year    calc_Year	Next Birthday in calc_Year	Age
Child1	        12-Feb-08	2010/11	    2010	12-Feb-10	                 2
Child1	        12-Feb-08	2011/12	    2011	12-Feb-11	                 3
Child1	        12-Feb-08	2012/13	    2012	12-Feb-12	                 4 

RE: Calculating Age for future dates

Hi,

What have you tried and what were the results?

Logic leads to evaluating if current is less than month & day then this year else next.

RE: Calculating Age for future dates

(OP)

thanks for the reply but I worked it out in excel and then applied it to access, basically I calculated a few different fields in the query

I had to just show the year for the childs DOB

CODE

YR: Year([dteChildDOB]) 

then I had to calculate the difference between the calc_year and YR

CODE

YRDiff: [strCalc_year]-[YR] 

and finally had to add the YRDiff to the child's DOB to show the next birthdays in the financial years

CODE

NextBirthday: DateAdd("yyyy",+[YRDIFF],[dteChildDOB]) 

I should have thought about it more but thanks again

the output I get below is from the query which matches my example

CODE --> datasheet

Childsname	dteChildDOB	strFinancialYear	strCalc_year	YR	YRDiff	NextBirthday
Child1    	12-Feb-08	2010/11	                2010	       2008	2	12-Feb-10
Child1  	12-Feb-08	2011/12	                2011	       2008	3	12-Feb-11
Child1   	12-Feb-08	2011/12	                2011	       2008	3	12-Feb-11
Child1  	12-Feb-08	2012/13	                2012	       2008	4	12-Feb-12 

Michael

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close