DateDiff not working on form

C

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
 
S

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"))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top