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

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.

terraksAsked:
Who is Participating?
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.

0
 
terraksAuthor Commented:
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.
0
 
terraksAuthor Commented:
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
'tblIntake.[DateDiff("yyyy",[YthBirthdate],Now())+Int(Format(Now(),"mmdd")<Format([YthBirthdate],"mmdd"))]'
0
 
Gustav BrockCIOCommented:
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:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26848591.html#a34984889

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.