y2k date issue

[namsilat]

I have an old database file in DBF format that I use Microsoft Access
to display. The dates in the DBF file only has 2 digits, so when it's
opened under Access, some dates are displayed as "19xx", while other
dates just as "xx". It creates quite a problem when I sort or search
by the dates. I do have a simple way to figure out whether the year
"xx" is 20xx or 19xx, so I was wondering if there is a way to add a
small macro for the date fields, such that when this DBF file is
opened under Access, proper conversion of the year would take place
automatically.

 

[John W. Vinson]

I have an old database file in DBF format that I use Microsoft Access
to display. The dates in the DBF file only has 2 digits, so when it's
opened under Access, some dates are displayed as "19xx", while other
dates just as "xx". It creates quite a problem when I sort or search
by the dates. I do have a simple way to figure out whether the year
"xx" is 20xx or 19xx, so I was wondering if there is a way to add a
small macro for the date fields, such that when this DBF file is
opened under Access, proper conversion of the year would take place
automatically.

Access uses (by a changeable default) 1930 as a cutoff: two digit dates 30-99
are assumed to be in the 20th century, 00-29 in the 21st.

If your dates just have the two digits, you need *SOME* sort of convention
that fits the "business rules" of YOUR data - which very will might be
different from someone else's business rules! Are the dates in this table the
birthdates of adults? If so, they're probably all 19xx - though my late friend
Anita was born in '97 and lived until '04; one of her
great-great-grandchildren was born in '97 as well.

Or are your dates bond maturity dates? Those will probably be in the future.

So there IS no "proper" conversion. That's why we had a Y2K crisis - the two
digit dates *are ambiguous* and require external information to remove the
ambiguity.

John W. Vinson [MVP]

 

[namsilat]

The "cut-off" in my DBF file is actually 1908, because there are
birthdays of people over 90 years old. There are some entries made
after 2000, and those dates despite having only 2 digits for years,
are displayed as "19xx" by MS Access. No dates were displayed as
"20xx" that I could see. For entires made prior to 2000, they are all
displayed as "xx" by MS Access regardless of which year it is.

I wonder if there is an external program that I can write up a small
macro to convert the DBF file before it's imported into MS Access.

 

[John W. Vinson]

The "cut-off" in my DBF file is actually 1908, because there are
birthdays of people over 90 years old. There are some entries made
after 2000, and those dates despite having only 2 digits for years,
are displayed as "19xx" by MS Access. No dates were displayed as
"20xx" that I could see. For entires made prior to 2000, they are all
displayed as "xx" by MS Access regardless of which year it is.

I wonder if there is an external program that I can write up a small
macro to convert the DBF file before it's imported into MS Access.

I don't understand. Have you imported the .dbf file into an Access table, or
just linked to it? Do you have a Date/Time field? What are some examples of
the dates?

You can set the Format property of a date/time field to display four digit
years. Try setting the format to "mm/dd/yyyy", or use Start... Control
Panel... Regional and Language Options to set the default short date format to
use four digit years. Do note that (unless you do so) dates like 2003 will
indeed display as 03.

Again... the cutoff is 1930. 00 through 29 will be displayed with just the two
digits, and will be assumed to be 2000 through 2029; 30 through 99 will be
stored as 20th century dates but will be displayed with four digits.

You can very easily run an Update query updating your 21st century dates. Use
a criterion

= #1/1/2000#

on the date field, and update the DOB field to

DateAdd("yyyy", -100, [DOB])

to set the date back one century.

John W. Vinson [MVP]