DateDiff not working on form

[Coles]

I have a function for working out the age of a person based on their date of
birth as follows:
=DateDiff("yyyy",[DOB],Now())+Int(Format(Now(),"mmdd")<Format([DOB],"mmdd"))

It works perfectly well for each record if I place it in a query but if a
put it as the default value on a form it will only work on the first record.

Can anyone enlighten me?

Many thanks,
Colin

 

[Steve Schapel]

Colin,

It is not clear for what purpose you are trying to use this expression
on the form. But the Default Value is only applicable at the point
where a new record is being created. Therefore you normally can't use
an expression that references the value of a field, since by the time
the value has been entered in the field, the opportunity for the default
value to have effect is already past. Hope that helps explain the
problem - if not, maybe you can post back with some more details.

By the way, the use of the Int() function in the expression doesn't seem
to serve any useful purpose.
(Format(Now(),"mmdd")<Format([DOB],"mmdd")) will always result in an
integer anyway, i.e. -1 or 0. You could just do it like this (I would
prefer Date() to Now())...
=DateDiff("yyyy",[DOB],Date())+(Format(Date(),"mmdd")<Format([DOB],"mmdd"))