Y2K problem - Importing DBASE III file

[benesch]

Hi:

I have been working with a database that I imported into Access 97 from DBASE III. There are several date fields in this file. Every time that a date is supposed to be 1/1/2000 or later, it shows as 100 years earlier (1/15/2002 shows as 1/15/1902). This is particularly annoying when the approval date is say 1/15/1997 and the expiration date is 1/15/2002 (the licenses in question are valid for 5 years). I haven't figured out a way to fix this problem within Access, and I don't have readily available DBASE III (or compatible (ie. FOXPRO 2.6) software. Does anyone have any suggestions?
Thank you in advance.

Andy Benesch

 

[dneufarth]

You could create UPDATE queries to add 100 years to date fields that are '1990' or before. Your test date may be different.


Dave

 

[PaulF]

why not just create an update query, and look for dates that you noticed as problems...2001 - 2005 or others and then update the date field with the date + 100 years

DateAdd("yyyy",100,[dateField])

be sure to make a copy of the table, before performing this operation, to ensure that you get the expected results

This SQL will update a field named NewDate in a table named Table14 to MyDate + 100 years if the year in a field named MyDate is less than 1925

UPDATE Table14 SET Table14.newDate = DateAdd("yyyy",100,[MyDate])
WHERE (((DatePart(&quot;yyyy&quot;,[MyDate]))<1925));

PaulF

 

[RickSpr]

Assuming you won't be having to import this again in the future, why not run a one-time update query that adds 100 years to any date prior to, say, 1/1/1950?

You can use the DateAdd() VBA function in the Update To: line of the query to compute the date 100 years later. Example: DateAdd(&quot;yyyy&quot;, 100, [fieldname]). Rick Sprague

 

[RickSpr]

Oops! Guess I'm a Johnny-come-lately. I shouldn't have interrupted composing my reply for dinner! Rick Sprague

 

[Guest_imported]

I will try using the Update Query that you suggested. Unfortunately, I do have to import the same flawed DBASE III file (with updated records) each quarter. Thank you for your help; I'll let you know what results I get.