I've got a table with a field for 'birthdate'
I want to create a calculated field for 'age'
this expression works
IIf(IsNull([birthDate]),Null,Year(DateSerial(2011,8,20))-
Year([birthDate]))

but how do I replace "DateSerial(2011,8,20)" with the current date ?
date() and now() don't seem to be valid functions ?

can this be done with a calculated table field ?

On 19/08/2011 12:33:41, Roger wrote:
Age: IIf(Not IsNull([BirthDate]),DateDiff("yyyy",[Birthdate],Date()))

Age in years

Phil

On Aug 19, 6:16*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
I think this works in a query, but not a table calculated field
when I try it as a calculated field, I get
the expression... cannot be used as a calculated column

On 19/08/2011 13:46:33, Roger wrote:
Bad policy to have calculated fields in a table. Why do you want it?
Tables are for storing data, very, very rarely should they be visible to the
user. The user interface is a form (or report).

Phil

On Aug 19, 8:16*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Some further testing will show that your expression does not produce
conventional age values when the birthdate is after the current date
in the year. That problem has been solved in more than one way in
this NG.

James A. Fortune
CDMAPoster (AT) FortuneJames (DOT) com

On 19/08/2011 19:12:51, "James A. Fortune" wrote:
Whoops, James, spoy on

Try

AgeateDiff("yyyy",[Birthdate],Now())+Int(Format(Now(),"mmdd")<Format([Birthdate],"mmdd"))

Anyway my main point was not using a calculated field in a table. Unless you
are very careful and say the birthday is entered as 1 Aug 2000 so the age is
11, and then you realise that is the wrong date and enter 1 Aug 2001, unless
you are using table triggers, as I understand it, what will induce the system
to recalculate the age as 10? Phil