none
Calculating AGE in a Table in Access using the DOB RRS feed

  • 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,

               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.

    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
  • 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 MSDNFSF@microsoft.com.

    Wednesday, January 24, 2018 8:50 AM
    Moderator