Imput mask not working correctly for certain values

[Charlie]

Within the database there is the control "=Date()-[DOB]", which uses the
information in the date of birth field to calculate the acutal age when it is
used with the input mask "yy".

This has worked fine up until I converted to the 2002 format. I now find
that anyone who is aged 29 or below has a date of e.g.1929, it is almost as
if the imput mask has stopped working, for these particular dates.

Although this does not stop the database from working accurately and the
reports still print appropriatley, it is an annoyance.

Any help would be appreciated, please note my knowledge of VB is limited at
best.

Thanks in advance

Charlie

 

[Charlie]

Hi Jerry

The DOB field is a date/time, the Age field where the calculation takes
place is text, once the date is entered into the DOB field as dd/mm/yyyy, it
places a date into the age field as dd/mm/yyyy, (the year always appears as
19xx).

Where the input mask is "yy" and the format is yy, this makes the age appear
as e.g 38, for those aged 29 or below, the age is coming up as e.g 1919.
e.g. the calculation : 08/07/1984-15/02/2006 = 09/08/1921 which currently
gives the age of 1921.
The calculation 17/08/1968-15/02/2006 = 30/06/1937 currently gives the age
of 37.

Any suggestions would be appreciated.

Charlie

 

[John Vinson]

Hi Jerry

The DOB field is a date/time, the Age field where the calculation takes
place is text, once the date is entered into the DOB field as dd/mm/yyyy, it
places a date into the age field as dd/mm/yyyy, (the year always appears as
19xx).

Where the input mask is "yy" and the format is yy, this makes the age appear
as e.g 38, for those aged 29 or below, the age is coming up as e.g 1919.
e.g. the calculation : 08/07/1984-15/02/2006 = 09/08/1921 which currently
gives the age of 1921.
The calculation 17/08/1968-15/02/2006 = 30/06/1937 currently gives the age
of 37.

A Date/Time value is stored internally as a *number*, a count of days
and fractions of a day since midnight, December 30, 1899. As such,
subtracting two dates gives a count of days, which indeed does
correspond to a date - but maybe not the date that you want!

Instead, use the DateDiff() function to calculate the number of years.
Since this calculates the number of year *boundraies* - New Years' Eve
midnights - between the two dates, you need a correction term.

Age: DateDiff("yyyy", [DOB], Date()) - IIF(Format([DOB], "mmdd" >
Format(Date(), "mmdd"), 1, 0)

The Age field should NOT be stored in your table at all: just
calculate it using the above expression as needed. Otherwise every age
in your table will be wrong a year from now!

John W. Vinson[MVP]