Calculating AGE in a Table in Access using the DOB

• Question

• I can't get the formula right for calculating the AGE field in the TABLE of my database.
Wednesday, January 17, 2018 4:53 AM

All replies

• ciao Linda,

applying this sql predicate, you can calculate the age :

SELECT Imp2.IDImpiegato,

Imp2.DataNascita,

Year(Date())-Year([datanascita]) AS eta,

DateDiff("yyyy",[datanascita],Date())+(Format\$([datanascita],"mmdd")>Format\$(Date(),"mmdd")) AS age
FROM

Imp2;

you could concrentrate just on bold calculated field, I have added some more information to highlight what the way is.

HTH ciao. Sandro.

Wednesday, January 17, 2018 7:37 AM
• You need an If condition to handle the current year.

For DOB 1/20/2000,

on 1/17/2018

age = (2018-2000)-1 = 17

on 1/20/2018

age= 2018-2000 = 18

IIf(Date < DateSerial(Year(Date), Month(Nz([DOB], Date)), Day(Nz([DOB], Date))), (Year(Date) - Year(Nz([DOB], Date))) - 1, Year(Date) - Year(Nz([DOB], Date)))

If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.

Wednesday, January 17, 2018 3:32 PM
• When asking questions, try to give us all the pertinent information as it help us customize the answer to your exact setup.  In this case, a table name, field name(s) would be most helpful.

You could use a VBA function and call it in a query expression, or build an expression directly in your query, such as

Year(Date())-Year([DOB]) + (Month(Date())<Month([DOB])) + (Month(Date())=Month([DOB]) And Day(Date())<Day([DOB]))

Where DOB would be the name of the field that hold the record's date of birth to calculate the age from.

Daniel Pineault, 2010-2017 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Wednesday, January 17, 2018 5:56 PM

I find that you did not follow up this thread after posting the issue.

If your issue is solved then I suggest you to post your solution and mark it as an answer.

If your issue is still exist then try to refer the solution given by the community members.

If then also you have any further questions then let us know about it.

We will try to provide further suggestions to solve the issue.