Calculate Current Age using Date of Birth

Please help. I have read every thread on this subject and NOTHING is working for me. I am using Access 2007. The two fields are AGE and DOB. DOB is to be stored in the table however age is only to be displayed on the form used to input the data. When the user puts in the DOB I would like the AGE field to automatically calculate that persons age.

So the previous database had =(Date()-[DOB])/365 in the AGE control on the form. It works in the old database but not in the new one (plus its not very accurate). Sorry to revisit this question but I must be missing something.

I tried =CInt(CLng(DateDiff("d",[DOB],Now()))/365 .25) and at first I got an error - so I took the space out before the 365. Now I once again have a #Name? error. The DOB in the table is in Date/Time (Short Date). Why won't any of the examples given work? I tried some basic expression in the field like =Date() just to see if it works and it does. I'm going mad over this!

ExplanationsExplanation
This sample includes two Visual Basic functions that help you calculate an age based on a date that you enter in a table. One of the funtions shows the number of years expired from today's date. The other function shows the number of months expired between today's date and the number of years expired. You can cut and paste either or both functions into a module in your own database.

In the Navigation Pane on the left double click on the module, basAge, to view the Age() and the AgeMonths() functions. The Age function returns the number of years between Now() and the name of the date field that you enclose in the Age() function. The AgeMonths() function calculates the number of years and months that have expired since the date you specify.

To see how the Age function works, click the View menu in the Visual Basic window while you are looking at the Age function, and click Immediate Window. (You can also type Ctrl+G to open the Immediate window.)

In the Immediate window type: ?Age(#11/15/67#) and press Enter to see the age in years.
In the Immediate window type: ?AgeMonths(#11/15/67#) and press Enter to see the age in years and months.

Go back to the the Contact List. In the Property sheet in design view of the form, you will see that the Record Source for the form is the Contacts Extended query. The Contacts Extended query contains an expression named "ContactAge," which calls, or references, the function, Age(). It also contains an expression named "ContactAgeWithMonth," which calls the function, Age() as well as the AgeMonths() function.

Open the query Contacts Extended in design view to see the ContactAge expression.
Click on the ContactAge expression in the field row.
Right click and select Zoom to see the whole expression in the Zoom window:

ContactAge: Age([Birth Date])

Right click on the ContactAgeWithMonth expression in the Field row and select Zoom to see the whole expression:

The ContactAgeWithMonth expression uses an ampersand (&) to concatenate, or connect, the return value from the Age function and the return value from the AgeMonths function along with the text for "years" and "months."

Note that each time you open the form, the calculation for both the fields, ContactAge and ContactAgeWithMonth occurs. If you have several thousand records, the calculation of all records may affect the performance of the form. If you notice a decrease in performance, do not include the calculation as part of the record source of the form and simply run a query to see the age with a click of a button. To achieve that goal:

Remove the Age and Age With Months labels and text boxes from the Contact List and Contact Details forms.
Copy the Contacts Extended query as Contacts Extended With Age.
Open the Contacts Extended query and remove the two columns that contain the expressions which call the Age and AgeMonths functions.
On the form header of each form, add a button to open the new query Contacts Extended With Age.

Using the Age() and AgeMonths() Functions to show Age of Orders
The following procedure explains how to see the age of orders by placing the age value in a new control on a form that references an Order Date.

1. In the sample database template Northwind2007.accdb, type or copy the Age() and AgeMonth() functions in a new module.

2. Open the Orders form in Design view and add an unbound text box control directly under the Order Date text box control.

Hi,
Have a look at your references to make sure they are all there as this can throw up some odd unrelated errors and make sure that the field Name for DOB hasn't acquired a space or other invisible character, it happens.
Also I double checked /365.25 (space not added by me) and it would be better if this were 365 anyway.
Best regards,
John

I have this note archived and it's working fine. Sorry, but I don't have
the author name to give him credits.

RETURNING A PERSON'S AGE

Many simple expressions are floating around that will return a
person's age. We've tested them all and found them all lacking in some
area. Eventually, these expressions will return an incorrect age, and
you may not know it.

The best route is to go with an accurate expression, even if it's
long, like the one we're about to show you. When working with age, you
can use an expression in the form

The information you want should be entered using a form. On the form create a textbox to link the field DOB. From your form in design view create your textbox next to the field DOB.

Name the textbox *txtDOB*, name text label *lblDOB* and change its caption to "AGE". Double click in the textbox and from the Property Sheet select the Data tab. In the Control Source box enter the following:

Hi,
You originally quoted
'however age is only to be displayed on the form used to input the data'
Is DOB actually referenced in the source query for this form as [DOB] will only be evident to the form's other controls if you have SELECT * or SELECT DOB, ... etc as the source?
Best regards,
John

Hi William, it's already set to plain text. Maybe I should just delete this db and start over! Thank you all for your help and suggestions! Nothing is working and I swear I am not a total dork. I know how to type an expression!

Like you I didn't use a query. I created a simple test table, even called this DOB to create confusion, and then used the 'create form' from the ribbon.
This form was automatically created with a record source property of DOB (my selected table). I also called my Date of birth field [DOB] and tested various formulas in an unbound, unpreformatted, text box and none failed.
I added a text box with a bogus field name used in the calculation e.g. =[NoSuchField] and then recieved the #Name? error.
In design mode look at any one of your bound controls and in the Control Source option click the drop down and see if your DOB field is in the list of available fields.

Though your issue is resolved, my second response to this thread did not get posted. Due to your limited experience I believe one of your "text fields" was named improperly which is why you keep getting the #Name?

For future use, here is link where you can download (Zip file) an Access database with 10 examples for calculating age. Some are complex and others are very simple.

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.