Calculate current age in whole years in Access
Q: I'm trying to calculate age in Access using this formula
Age: (Date()-[SPBPERS_BIRTH_DATE])/365.25
This runs from a make-table query. The table is formatted as Double with zero decimal places with the intent of getting a whole number.
The result is 20.7583846680 instead of 20. Please help.
A: You're probably better off using the DATEDIFF formula, and wrapping it in a ROUND. This should do what you need.
Round(DateDiff("y",[SPBPERS_BIRTH_DATE],Date())/365.2425-0.5,0)
Since ROUND will always round to the nearest integer, I subtract 0.5 at the end which has the effect of the expression always rounding down, which is what we tend to do with ages, of course. You could - if you prefer - simply wrap such a ROUND function around your existing formula.
I've used 365.2425 because it's a closer approximation, and with a centurial leap year just having passed, I have a notion that more accuracy is better, although I haven't been bothered to prove it makes a difference I'm afraid!