Access 2003 - How to see a form's calculated age field value on a table field?

Posted on 2011-04-22
Last Modified: 2012-06-27
Dear Experts:

I have figured out how to calculate the age from the birthdate on a form field. Now, I would like to show the calculated age on a table field.

How do I connect the age's form field (with the age formula* on the Control Source)  to a table field (YthAge)?

* =DateDiff("yyyy",[YthBirthdate],Now())+Int(Format(Now(),"mmdd")<Format([YthBirthdate],"mmdd"))

Thank you.

Question by:terraks
    LVL 84
    I'm not sure what you mean - do you want to store the Age value in the table? There is rarely a reason to do this (i.e. store calculated data in a table), when you can easily create it on the fly.


    Author Comment

    What I want is to create a query with the calculate age as a field to later run a report with the ages on it, not necessarily the birth dates. I don't know how to transfer the calculate age value from the form to the query.

    I thought it might be easier to put the calculate age value on a table to then create a query, hence the initial question.
    LVL 84

    Accepted Solution

    To do that, just add a Field to your query. If you're doing this in the Query Designer, just add a new Column in the grid:

    1) Move to a blank "column"
    2) In the "Name" row of that column, type this:

    MyAge: DateDiff("yyyy",[YthBirthdate],Now())+Int(Format(Now(),"mmdd")<Format([YthBirthdate],"mmdd"))

    This will add a new field named "MyAge" to the query, and you can use that on your report as needed. The query would need to be based on the same table that contains your field "YthBirthdate", of course.


    Author Comment

    Thank you! It totally works.

    Just in case, I did not add the table name (tblIntake -- I am only using one table) to the Table field on the query designer because I got this error:

    Syntax error (comma) in query expression
    LVL 48

    Expert Comment

    by:Gustav Brock
    Please be aware that this method for calculating age may fail. It may not be important for your case, but if the calculation must be 100% correct read on here:


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now