Full Version: Calculating age from date of birth to a set future date
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
Rynorem
Here's what I've got.
In the Query field:
I need to calculate an age from the DOB (date of Birth) to a specified date (3/1/2010).
I've used DateDiff("yyyy",[DOB],DATE())+(Format([DOB],"mmdd")>Format(DATE(),"mmdd")) in the query and it gave me thier current age. But I need it from the future date of Mar. 01 2010 (3/1/2010). When I replaced the DATE() with 3/1/2010 it gave me a large negative number. Please help.

Jamie
debra_dixon
Did you put # around the set future date?
Rynorem
Yes with the same results---negative number
truittb
This works for me

DateDiff("yyyy",[DOB],#3/1/2010#)+(Format([DOB],"mmdd")>Format(#3/1/2010#,"mmdd"))

The only way I could get a negative number was to leave off the # delimiters.

You might check the date in DOB if it still does not work.
akn_39
Would this work for you:

Age() Function (Allen Browne)

Tony N.
Rynorem
Thanks, Truittb. I copy paste it into the field and it worked. I compared the two and couldn't find a difference but it work so thanks.
truittb
You are welcome.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.