Error when Importing Excel sheet into Access

[Guest]

I am having trouble with one date column when importing an excel file into
access. The problematic column is formatted as "date" in excel (the exact
same way as another date column in this particular excel file) however, when
I import it into the access table the date column is converted to text and
only about 20 of the 400 rows have a valid date entry. All the other rows
have the text version of the date - when I try to convert the entire column
in access to a "date" column (instead of text) all the text formed dates are
deleted.

Any thoughts on how to fix this?

 

[Jamie Collins]

I am having trouble with one date column when importing an excel file into
access. The problematic column is formatted as "date" in excel (the exact
same way as another date column in this particular excel file) however, when
I import it into the access table the date column is converted to text and
only about 20 of the 400 rows have a valid date entry.

A column in an Excel's sheet may hold mixed data types. A column in a
Jet (MS Access) column may not (although some types will coerce e.g.
TEXT to MEMO). I assume your column is defined as TEXT and the 20 or
so that appear in your preferred date format are mere happy
coincidences.

When Excel decides the data type for a column, while it pays some
regard to the column's number format, it is the cell values themselves
that really count. With the worksheet open in the Excel UI, try
changing the column's data type to numeric. True dates values will now
appears as numbers (usually in the 30000 range); any values still
appear as text dates are in fact text values.

Jamie.

--