Numbers displaying as Dates and vice-versa when linking excel to Access

[cegbuna]

I have an excel (2010) spreadsheet with approx 8000 rows and 50 columns. I have formatted the spreadsheet based on the data type (number, dates, texts...etc). When I linked this spreadsheet to Access (2010), the wrong format is displayed (number cells/columns are displayed as dates and vice-versa). Does anyone know the fix to this problem?

I appreciate your comments.

 

[xenou]

Does each column in Excel have one and only one type of data (Numbers, Text, or Dates)?

 

[cegbuna]

Yes.

 

[xenou]

Have you tried setting the display formats in Access?

 

[cegbuna]

I am linking the spreadsheet to access, so I can't modify the formats on the design view screen.

 

[xenou]

It's quite mysterious. I'm not a fan of linking Access to Excel. Can you just work in Access? My only thought right now is to start from a new spreadsheet and see if it works better with a new try. I have Office 2010 at home (but I'm not there right now). I'll give it a test tonight. But I can really only believe that its going to work fine for me when I link Access to a worksheet ... it sounds one of those strange things that don't always find a good explanation.

 

[Joe4]

Importing and linking to Excel from Access is often problematic, as Access tries to "guess" the format of each field, and sometimes guesses wrong (especially if you have blank in your data).

If all else fails, here are a few options.

1. Import the Excel file to a temp Access table, then write that data from the temp table to your final table, with all the formats you have (via an Append Query to a table structure you have already defined).

-or-

2. Export the Excel file to a text file. Import the text file into Access, where you can control the imports on all the incoming fields.

 

[xenou]

Well, for what it's worth I linked my Access 2010 to an Excel 2010 worksheet with numbers and dates ... no problems (but I didn't expect any). Also, by the way, despite Access warning that I can't change properties, I was able to change the way my dates displayed by changing the format in Access.

 

[cegbuna]

Were you able to save your new format in access afterwards?

Thanks

 

[xenou]

Yes. In Excel my dates were formatted m/d/yyyy and I changed (and saved) the format in Access to display the dates as dd-mmm-yyyy.