Calculate age from Date of Birth

[djcmalvern]

Using only built-in functions and expression builder how can I create a field in a table that automatically calculates the age of a user after they type in their Date of Birth. Can this be done with a calculated field using the DateDiff() function on the fly? OR, can this be done on a form with a calculated field? I tried this but got lost and could not get the Age field to update the value in the bound table or display in the text box to which I was attaching the expression. So how does this all hang together please? How do you get the calculated value to update into the table and display on the form after updating the Date of Birth field/control?

[June7]

VBA code would be required to save value into table.

Should not save the calculated result into table. Calculate whenever needed.

The calculation can be in query or textbox. Not sure if Calculated field in table can handle the Datediff function.

[rpeare]

On your form have a text box that with a control source of

=Datediff("yyyy", [birthdate], date())

As june says, you do not want to store calculated values in your tables. Ever.

The same formula can be used in a query and given an alias like :

PersonAge: datediff("yyyy", [birthdate], date())

[Missinglinq]

...=Datediff("yyyy", [birthdate], date())...

Done like this, DateDiff() is simply subtracting the year of birth from the current year! The answer will only be correct if the person has already had their birthday for the current year!

Here are three Formulas commonly used for an accurate calculation of age:

DateDiff("yyyy", [DOB], Date) - IIf(Format$(Date, "mmdd") < Format$([ DOB], "mmdd"), 1, 0)

 DateDiff("yyyy", [DOB], Date()) +  Int( Format(Date(), "mmdd") < Format( [DOB], "mmdd") )

DateDiff("m", Me.DOB, Date()) \ 12

Linq ;0)>

[djcmalvern]

Thanks everyone! Got the idea. Great help.

[Missinglinq]

Glad we could help!

Linq ;0)>

[shafiq037]

please clarify that your code is not working in single test table, why? I am using win7 32 bit and access 2010.

[ssanfu]

One more formula:
https://www.accessforums.net/access/...age-34321.html

[Missinglinq]

All code given is time tested; either

1. You've done something incorrectly, possibly using incorrect syntax
2. You've misspelled a field or table name
3. You have your code in the wrong place
4. You don't have the folder holding the file declared as 'Trusted'

If the folder has been marked as Trusted (does any code run???) you'll need to copy and paste the code you're using in order for us to trouble-shoot it.

Linq ;0)>