Calculate age from date of birth in a table

[oldlearner]

Hi There, can anybody give me the correct expression for calculating age from date of birth in a table (not using a queire). I have tried INT((Date()-[DOB])/365.25) but it does not appear to work.

Thanks for any assistance.

[June7]

Seems to work for me. So what does 'not appear to work' mean - error message, wrong results, nothing happens?

[oldlearner]

Hi june. I am getting the message "the expression cannot be used in a calculated column". Am I selecting the wrong data type to use the expression or could it be something else?

[June7]

You are doing this in table calculated type field? It does not recognize the Date() function. Not all functions are available to calculated field in table. Do this in query or textbox.

[oldlearner]

Thanks June will give that a try.

[gpeirce]

I've had very good luck with:

Age as of Today: DateDiff("yyyy",[dob],Now())-IIf(Format([dob],"mmdd")>Format(Now(),"mmdd"),1,0)

This subtracts the birth year from the current year. If the person's mmdd birthday has not been reached yet, 1 is subtracted.

Hope this helps.

[oldlearner]

Thanks guys. used a text box on a form and is working fine.
Can you advise if I would need to create and perform a Macro (also advise on contents of macro) to update the ages periodically?

[June7]

If by update you mean save age to table - don't. Calculate when needed.

[oldlearner]

Hi June- Sorry I need to make myself more clear with what I am asking. I actually meant do I need to create a macro to update the ages periodically so they are more accurate when someone has a birthday and is 1 year older.

[Homegrownandy]

What Jane said is right, just have your report calculate this when opened each time. Don't actually save this information.