Import Excel text field to Access date field

  • Thread starter Thread starter djh
  • Start date Start date
D

djh

I am importing Excel spreadsheets into Access database
tables. The Excel files have cells format as strings,
but they are actually dates in the format yyyymmdd. How
can I import these as dates into Access so that I can do
date comparisons and can display them as mm/dd/yyyy? I
keep getting data conversion errors regardless of how I
define the fields in Access.
 
Import the Excel data into a staging table as TEXT.
Then write a query to parse the text into the correct date format and append
the query to the real table.
 
Or save the Excel file as a tab delimited text, and import the text file
with the desirable import specification in Access.

After having worked for a while with Access, I think that you have most
control over how to import text files, while it depends a lot on how Excel
files are created to be imported in the desired way in Access.

Herbert
 
So to confirm, if I have a field in an Access table
defined as a string which is in the yyyymmdd format,
there is no data conversion I can do within that Access
table to convert it to a date by changing the Data Type,
Format, Input Mask, etc.?
 
Back
Top