Macro for auto updating age based on Birth Date field

[Locus88]

I have an Access database, and the database has a Birth Date column. Age is a dynamic column. How can I insert a new column into the database, which will automatically update the ages based on the Birth Date field.

IF Macro is needed, can someone provide the instruction? I would like the age field is automatically updated each time when the database is opened.

thanks very much! A step by step instruction is very much appreciated.

 

[Access_guy49]

Your best to create a query that has a calculated field.

Calculated values that change over time shouldn't be "stored" in the database. they should simply be generated based on the data that doesn't change, which is the birth date.

In a new query, select your table, and ALL the fields. Then in the next space to the right for a new column, create one
You will need to look into the DateDiff function.

in the query your "Field" would look something like this:

Expr1: DateDiff("yyyy",[Table1]![Bdate],Now(),0,0)

But you would change Exprs1 to be your column heading name you want to display, and your table name and column name will be different. I used BDate as my column name to store Birthdate. Now() is a built in function that gets todays date.

 

[Brianwarnock]

Datediff does not calculate age properly, merely subtracts the year

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

Checks whether the birthday has passed or not.

Brian

 

[Locus88]

Thank you very much. Let me try that.

 

[Locus88]

The problem is I have somo records with blank Birthdate, how can I still keep all the records with the formula below? Thanks very much!

Datediff does not calculate age properly, merely subtracts the year

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

Checks whether the birthday has passed or not.

Brian

 

[Brianwarnock]

Its years since I used it but I think it just returns Null, if not you'll have to wrap an IIF round it

Brian

 

[Access_guy49]

Datediff does not calculate age properly, merely subtracts the year

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

Checks whether the birthday has passed or not.

Brian

OOO... very nice! Learn something new every day!