Changing dates


I don't know whether there is a connection with my problem expressed below but I have recently upgraded from Paradox 8 to Paradox 9 and from MS Office 97 to Office XP (aka 2002). I'm running W2K.

I've just imported about 4500 records from an Excel spread*** to one of my databases. The date field in Excel is formatted as dd/mm/yyyy, the same as my preferred Paradox format.

However, on importing this file, the format appeared in Paradox as mm/dd/yy and the field was alphanumeric. I changed the field to Date and the format changed OK to dd/mm/yyyy, BUT all dates which in the spread*** ended in '2004' were, in the Paradox table created by my import, shown as '04'. When I changed that field to Date, the years have come out as '0004'.

The above has never happened before - I've carried out the same kind of import many times. Also, it still works properly, with Paradox recognising the Excel format of dd/mm/yyyy and formatting the date field correctly in the import table, on another computer still using Paradox 8 and Office 97.

My regional settings in Windows Control Panel are for the UK with the short date format of dd/mm/yyyy and with the option for converting two digit years correctly checked.

The Properties of the date field in my Paradox table show as Windows short date format. And the help file says that "For each format, a two-digit yy value from 00-50 (2000-2050) is assumed to be in the twenty-first century."

I wondered whether it was an Excel problem rather than a Paradox problem, so I tried saving the Excel file as Excel 95/97 as this is what Paradox listed, rather than Excel 2002, but with no effect, and I also saved the spread*** as a tab-delimited text file. When I imported that into Paradox, it converted all the dates from dd/mm/yyyy to mm/dd/yyyy, but, as it had correctly declared the field to be a Date field, only those dates that fitted both the mm/dd/yyyy and dd/mm/yyyy formats, e.g. 03/11/2004, were included, the rest were shown as blank.

I'd be grateful for some indication of why this has happened and how to stop it happening again and, additionally, whether there is any way of adding 2000 to the year element in a date field so that I can correct the 4500 entries in the database rather than having to delete them!

--
Malcolm
.