Importing error. Unable to resolve.

[Ayo]

I am trying to import an excel spreadsheet into access, even though the dates
data have been formatted as dates, the import process is still giving me
"Type Conversion Failure" errors. How do I fix this. I keep going back and
fort between excel and access without any resolution. I need help. I am
getting beat down here. Thanks

 

[Jerry Whittle]

What do the dates look like in Excel? Post a few of them especially those
that you know are problems.

You might want to consider either importing the data into a text field OR
linking to the Excel spreadsheet. Then you can run a query on the data using
the IsDate function. Something like below will show what data can not be
evaluated as a date.

SELECT tblDatesText.ex_date, IsDate([ex_date]) AS NotDate
FROM tblDatesText
WHERE IsDate([ex_date]) = False;

Another thing to watch for is Access expects D/M/YY (day / month / year)
format. 12/13/2007 is a valid date; however, 13/12/2007 is not. Also 1/2/2008
Would be January 2, 2007 and not February 1, 2007.

 

[Ayo]

It looks like most of the dates starting with 11/, are the ones not being
recognized as dates.
11/20/2007
11/13/2007
11/26/2007 and so on

What do the dates look like in Excel? Post a few of them especially those
that you know are problems.

You might want to consider either importing the data into a text field OR
linking to the Excel spreadsheet. Then you can run a query on the data using
the IsDate function. Something like below will show what data can not be
evaluated as a date.

SELECT tblDatesText.ex_date, IsDate([ex_date]) AS NotDate
FROM tblDatesText
WHERE IsDate([ex_date]) = False;

Another thing to watch for is Access expects D/M/YY (day / month / year)
format. 12/13/2007 is a valid date; however, 13/12/2007 is not. Also 1/2/2008
Would be January 2, 2007 and not February 1, 2007.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to import an excel spreadsheet into access, even though the dates
data have been formatted as dates, the import process is still giving me
"Type Conversion Failure" errors. How do I fix this. I keep going back and
fort between excel and access without any resolution. I need help. I am
getting beat down here. Thanks

 

[Jerry Whittle]

Very strange as they look like perfectly valid dates in mm/dd/yyyy formats.
What are the regional settings set to for that computer? Maybe it's set to
something other than English(USA).

This one is way out there, but could the 11's actually be lower case L's?
With some fonts they look almost the same.

Otherwise I recommend using something like the SQL statement below to see
which records are problems. Then investigate.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It looks like most of the dates starting with 11/, are the ones not being
recognized as dates.
11/20/2007
11/13/2007
11/26/2007 and so on

What do the dates look like in Excel? Post a few of them especially those
that you know are problems.

You might want to consider either importing the data into a text field OR
linking to the Excel spreadsheet. Then you can run a query on the data using
the IsDate function. Something like below will show what data can not be
evaluated as a date.

SELECT tblDatesText.ex_date, IsDate([ex_date]) AS NotDate
FROM tblDatesText
WHERE IsDate([ex_date]) = False;

Another thing to watch for is Access expects D/M/YY (day / month / year)
format. 12/13/2007 is a valid date; however, 13/12/2007 is not. Also 1/2/2008
Would be January 2, 2007 and not February 1, 2007.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to import an excel spreadsheet into access, even though the dates
data have been formatted as dates, the import process is still giving me
"Type Conversion Failure" errors. How do I fix this. I keep going back and
fort between excel and access without any resolution. I need help. I am
getting beat down here. Thanks

 

[Ayo]

I honestly don't know what is going on. Right now I am having the same
problem with another file. In this case I was able to reduce, and narrow
down, the error to just two cells in the excel file. Even when I went into
the excel file and retyped the date as 1/4/2007 and formatted it to date,
access still won't recognize it. There are about 7200 records in the file and
I am having this problem with just two cells.
I can't understand this at all.

Very strange as they look like perfectly valid dates in mm/dd/yyyy formats.
What are the regional settings set to for that computer? Maybe it's set to
something other than English(USA).

This one is way out there, but could the 11's actually be lower case L's?
With some fonts they look almost the same.

Otherwise I recommend using something like the SQL statement below to see
which records are problems. Then investigate.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

It looks like most of the dates starting with 11/, are the ones not being
recognized as dates.
11/20/2007
11/13/2007
11/26/2007 and so on

What do the dates look like in Excel? Post a few of them especially those
that you know are problems.

You might want to consider either importing the data into a text field OR
linking to the Excel spreadsheet. Then you can run a query on the data using
the IsDate function. Something like below will show what data can not be
evaluated as a date.

SELECT tblDatesText.ex_date, IsDate([ex_date]) AS NotDate
FROM tblDatesText
WHERE IsDate([ex_date]) = False;

Another thing to watch for is Access expects D/M/YY (day / month / year)
format. 12/13/2007 is a valid date; however, 13/12/2007 is not. Also 1/2/2008
Would be January 2, 2007 and not February 1, 2007.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

:

I am trying to import an excel spreadsheet into access, even though the dates
data have been formatted as dates, the import process is still giving me
"Type Conversion Failure" errors. How do I fix this. I keep going back and
fort between excel and access without any resolution. I need help. I am
getting beat down here. Thanks