access 2002 dates are wrong after upgrade

  • Thread starter Thread starter Frank F
  • Start date Start date
F

Frank F

After upgrading an MS access database from 97 to 2002 a column that shows
when students enrolled by date some of the dates are correct 2002 and some
show
dates such as 1903, 1901 which should reflect 2003, 2001. Any ideas on how
to correct this to show the actual enrollment dates?
 
You sure they were correct before? Dates are actually stored as 8 byte
floating point numbers, where the integer part represents the date as the
number of days relative to 30 Dec, 1899 (and the decimal part represents the
time as a fraction of a day). There's no way converting from one version of
Access to another can affect the date.

What you could try doing is running an Update query to change all of the
dates that are wrong. Not knowing how far back your database goes, I'll
assumg that you might have some legitimate dates in the 1990s, so try
something like:

UPDATE Registrations
SET RegistrationDt = DateAdd("yyyy", 100, [RegistrationDt])
WHERE RegistrationDt <= #1/1/1989#

And make sure you use 4 digit years from now on.
 
Thanks Doug,

I will investigate more on Monday and try the update registrations.

Frank
Douglas J. Steele said:
You sure they were correct before? Dates are actually stored as 8 byte
floating point numbers, where the integer part represents the date as the
number of days relative to 30 Dec, 1899 (and the decimal part represents the
time as a fraction of a day). There's no way converting from one version of
Access to another can affect the date.

What you could try doing is running an Update query to change all of the
dates that are wrong. Not knowing how far back your database goes, I'll
assumg that you might have some legitimate dates in the 1990s, so try
something like:

UPDATE Registrations
SET RegistrationDt = DateAdd("yyyy", 100, [RegistrationDt])
WHERE RegistrationDt <= #1/1/1989#

And make sure you use 4 digit years from now on.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Frank F said:
After upgrading an MS access database from 97 to 2002 a column that shows
when students enrolled by date some of the dates are correct 2002 and some
show
dates such as 1903, 1901 which should reflect 2003, 2001. Any ideas on how
to correct this to show the actual enrollment dates?
Click to expand...
Click to expand...
 
Back
Top