Importing Date field into Ms Access

[Haho]

I am trying to import date field into Ms Access from CSV file.
The date has the following format yyyy-mm-dd and value of 0001-01-01

Ms Access imports this into 2001-01-01 which is wrong i want to import this
as it is: 0001-01-01.

Is there any way to stop access converting this.

Thanks

 

[Klatuu]

You can't. The earliest possible date in Access is January 1, 100 and the
latest is December 31, 9999

 

[Haho]

OK then i want access to import this as error or not import the date to leave
it blank. But i don't want to convert it !

This autoconvert option is causing the problem, becasue it's importing it as
a valid date which as you said 0001-01-01 is not.

Is there any way of stopping this auto correct, auto convert option.

 

[Haho]

Is there any way to stop the auto convert function ?

I want this import to fail or to import it as error or blank, but instead Ms
Access is converting the value and it's importing it as if it is a valid date
?

Which is wrong.

Thanks

 

[Klatuu]

if you are using TransferText to import the CSV file, you could import to a
temporary table with the field defined as text. Then use a select query to
find the errors and correct them.

 

[Haho]

I am planning to do this ... but this is rather complicated procedure to
resolve such a simple issue.

The problem is that Ms Access is trying to be smart and it is autocorrecting
the invalid date.

I prefer to switch off the auto correct function if there is a way to do this.

Thanks.

 

[Klatuu]

Autocorrect has nothing to do with this. You should have it turned off;
however, because it can cause other problems. Uses Tools, Options and select
the Spelling tab. Set the options so it doesn't check anything.
What you are actually seeing was put in to prepare for Y2K. I don't
remember the exact cut off, but I think it was about 1940 +/- where when you
enter a two digit year, anything less than 40 would be seen as 20?? and
anthing greater would be seen as 19??. So that is what is happening.
Because the value is 0001-01-01, what Access is reading is 1/1/1.

There is nothing wrong with Access in this case, it is bad data.

 

[Haho]

Hi David,

I think what is actually happening is Ms Access trying to convert the
invalid date 0001-01-01 into a valid date (to 'auto correct' the error) and
the closest it gets is 01-01-2001.

Ideally i wish Ms Access to acept this as a valid date 1 January year 1 AD,
but it doesn't. Or not to import it at all or report error.

I tried importing 0100-01-01 and it works fine it imports this as 1 January
year 100 AD.

I think Ms Access has some build in error handling when it finds date that's
wroing it automatically tries to convert it into a correct date.

I want to stop this auto convert option.

Thanks for you help. It was very useful.

 

[Klatuu]

It is not error handling. It is the way it works as I explained previously.
Date range is 1/1/100 - 12/31/9999
0001-01-01 is seen as 1/1/01 (m/d/y)
Since 01 is < than the Y2K cuttoff, it returns
1/1/2001
Dates are stored internally as floating point decimal numbers, not as we
humans see them.
Your only option is to use some method of identifying and correcting the bad
data.
Good Luck.

 

[Haho]

Hi David,

Thanks for your help i think you are right.

Access sees 0001-01-01 as 1/1/01

I want access to see 0001-01-01 as 1/1/0001 as it is, but i guess it's
programmed to see it as 01/01/01.

I will use a temporary table, import the field as text, remove the invalid
dates and then append into the normal table as Date field.

 

[Klatuu]

Good plan. Post back if you have problems with it.