[dfriend846]

I am linking an Excel table to Access and one of the columns contains both numbers (e.g. 93500) and text data (e.g. P934a30). See images. My problem is that the link is set up successfully but Access considers the problem column to be numeric and displays $Num! for the text values. I cannot change the field type in Access. I have changed the Excel column format to Text but that does not help. I have seen this before so I suspect it is a common issue that I just do not know the answer for. My reason for doing a link is that the Excel tables will be updated periodically and this seems the easiest way. Can anyone offer any suggestions?
Dennis

 

[etaf]

when you link the excel file - you have an option in the wizard to set the field type for that column - if memory serves me correctly - not on a PC with access currently

 

[OBP]

Dennis, you need to "Cheat" here, delete the link to the current Excel Sheet.
In the Problem Coulmn(s) enter a "'" in front of the the number in the first rows).
This makes the Number entry store as "text" and you will get a "warning" in the cell if you have them turned on.
Save the Workbook.
Now link to the Workbook and Access will set it up as a Text Field.
If anybody deletes the "'" or overwrites it with a number you will get the original error come back.
Personally at Access startup I would Import the Excel data in to a temporary table, Update a proper Access table with a query and then delete the temp table.
The only problem is the access table won't reflect changes to the Excel sheet until you close and re-open it.

 

[slurpee55]

An easier answer - in the Excel file, highlight the column and go to Format Cells for the Local ERP #. Go to Custom and type in & as the format. Link it to Access - it works fine.

 

[Drako]

i am pretty much a noob in access but cant you go into design view and change the column to text?

 

[dfriend846]

Hi OBP, I have tried your second solution and it works but the first seems not to: If you note (green image) the first row of the import is a text value. Should I still need to put a ' before it?
dennis

 

[dfriend846]

Unfortunately not for a linked Excel worksheet. You can change the format on an imported table but not a linked one.

 

[dfriend846]

Interesting, I used an & in the custom format and it replaced all the number values with &. I will check further after work to see if they come out as the proper answer in the Access table.

 

[slurpee55]

weird - I got it to work using the "&" earlier, but now it doesn't want to....