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.

Answer 1

ciao Linda,

applying this sql predicate, you can calculate the age :

SELECT Imp2.IDImpiegato,

          Imp2.DataNascita,

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

           Format$([datanascita],"mmdd")>Format$(Date(),"mmdd") AS toAdd,

           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.

Answer 2

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.

Answer 3

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

Answer 4

Hi SLindamood,

Is your issue solved?

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.

Thanks for your understanding.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].