Web Development

Ask something
Discussion Locked

Access 97 update query to fix 1901 date

0 helpful
3 replies

by loydster 2 views

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

This outta be a bit nostalgic for some.

I have a database in Access 97 that is converted from an old Dbase IV database. It has a date it converts to 1901 instead of 2001. (or 1900 instead of 2000)

How can I update all 4000+ records at the same time? Basically, I need an update query to look at the last 4 digits of a date and change it. Unless there is a better way.

Also, does anyone know a good Access 97 site?

3 replies

by eebee

Try this:
I've created a table [Test] with 2 fields:
[OldDate] as date and [NewDate] as date (its better to create a new field so you can test it before doing any changes to the original field).

Then I created the following Query:
UPDATE TestSET Test.NewDate = Left(Format([OldDate],"mm/dd/yyyy"),6) & (CLng(Right(Format([OldDate],"mm/dd/yyyy"),4))+100);

Be sure that the format is ok for your country: mm/dd/yyyy, you may change this to dd/mm/yyyy.
Also filter the data to change only the 1900 and 1901 dates.

Let me know if this worked for you...

This discussion is closed to new replies.