I have a formatting nightmare in Excel and / or Access and could use some suggestions on how to resolve

[DLB20720]

Access and Excel 2013

I have 2 Excel workbooks I ‘link’ to tables in Access. On my first attempt to set this up in Access, I noticed 10 of my 55 number columns were being displayed as text (left of cell). I checked the formatting of the table in Access and confirmed they were being linked as text. My fault, I went back to Excel and changed the formatting on the 10 columns to a ‘number’ format and refreshed the links in Access. When I opened the table in Access, now everything displayed as a number (right of cell) but when I checked the formatting, those 10 columns are still formatted as text.

Long story short on the rest of my sequencing, I combined 2 tables in an Access Query and tried changing the formatting in the Query, but cannot do that. I then exported the Query to another Excel workbook hoping that I could deal with the formatting issue in my final version of Excel, but it was worse! My data is now displayed as a number (right of cell) and is formatted as a number, but … I get the ’green error corner’ that shows the cell contains a number displayed as text! Even though it displays as a number, I get that error symbol. If I run an ‘ISNUMBER’ function on any of the cells, it returns as FALSE. Even though it is displayed and formatted as a number, I tried to ‘change’ the format to a number but that has no effect. If I copy and paste the values to a blank cell, they display as text, are formatted as ‘General’, and do not respond to attempts to change the formatting to Number. If I run the ISNUMNER test on the data in the original Excel workbook, it returns as TRUE indicating it is being stored as a number in the original workbook.

Out of curiosity, I tried importing the workbook into Access rather than linking and it is the exact same thing when exported. Even though importing allows me to change the column data type to ‘Number’ it still is exported as text.

I’ve been beating my head against the wall on this issue for about 4 hours now and I am stumped. None of this makes any sense to me and I’m hoping someone may have some advice on how to overcome this issue. Thanks to anyone who can offer advice!

 

[DLB20720]

I believe I found the issue and also a temporary work-around but if anyone can offer a better solution, I’m open to it.

Apparently Access checks the first 8 rows of an Excel worksheet to determine data type, regardless of how I may have my columns formatted. In the 10 ‘problem’ columns I have, there are 4 or more blank cells. I’m assuming this causes Access to apply a Short Text data type to these columns.

I achieved a temporary work-around by creating 10 fake rows (top rows) in my workbook and used a numeric value in all 55 columns that should be numerical values. Now Access reads these fake values and sets the data type to a numeric value. Unfortunately, this same workbook uses all records for my Join Properties and only matching records from the 2^nd workbook so these fake rows appear in the Query and export.

I think I can create a 3^rd Excel workbook and use that to control Join Properties and the Query and export, but it will be more difficult to make sure the true records match in both workbooks.