Newbie question

[Chuck M]

In my form I have calculated a field to show years of service. It is
calculated as follow,

=DateDiff("d",[Hire Date],[Todays Date])
Formated: yy"Y "mm"M "dd\D

as long as the hire date is less than 1/1/1976 it is fine, if it is 1/1/1976
are greater it will put a 19xx as the year.

I have tried everything I can think of, I'M STUMPED

Can some one help, any ideas.

Thanks
Chuck

 

[John Vinson]

In my form I have calculated a field to show years of service. It is
calculated as follow,

=DateDiff("d",[Hire Date],[Todays Date])
Formated: yy"Y "mm"M "dd\D

as long as the hire date is less than 1/1/1976 it is fine, if it is 1/1/1976
are greater it will put a 19xx as the year.

DateDiff does not return a date. It returns a *count of days*. A
Date/Time value is stored as a Double Float number, a count of days
and fractions of a day since midnight, December 30, 1899; so you'll be
*sort of close* by formatting it in this way.

However, the problem is your use of the two-digit year format. Dates
with a two-digit year of 00 through 29 are assumed to be in the 21st
century; dates with years 30 through 99 are assumed to be in the 20th.
As a result, employees with 30 years or more of service are displayed
with "dates" (what you intend as durations, but what Access is
interpreting as a specific date) like 1931 instead of 31.

I'd suggest using instead Doug Steele's "Better DateDiff":

Check "A More Complete DateDiff Function" at
http://www.accessmvp.com/djsteele/Diff2Dates.html


John W. Vinson[MVP]