[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 225
  • Last Modified:

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.

0
terraks
Asked:
terraks
  • 2
  • 2
1 Solution
 
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
 
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
 
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

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now