Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel linked to Access
I linked Excel table and am having an issue with one of the field format
conversion as follows: The field in question is an alpha numeric ID field/column in Excel with some all number, all text and combined number and text cells formatted as General i.e MLE, 4523, S8T1 etc...When you open the linked spreadsheet in a direct linked table this field shows records with the error #NUM! possibly because during the field conversion Access assumes that it's a number field dropping the all text. I tried converting the entire column to text in Excel and now it gives me an error on the all number fields. MLE #NUM! SSM #NUM! I don't see any other format in Excel that would accomodate this circumstance. Please help...thank you. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel linked to Access
One possible solution is to format the column as text and for each numeric
value (e.g. 4523) edit the cell value to put an apostrophe in front (e.g. '4523). By putting an apostrophe as the first character you are converting the numeric value to text and it will show up in Access. If you have many values, you may want to consider writing a macro to convert the numeric values in the column. You can test for numeric values using the IsNumeric function. -- David Lloyd MCSD .NET http://LemingtonConsulting.com This response is supplied "as is" without any representations or warranties. "AccessARS" wrote in message ... I linked Excel table and am having an issue with one of the field format conversion as follows: The field in question is an alpha numeric ID field/column in Excel with some all number, all text and combined number and text cells formatted as General i.e MLE, 4523, S8T1 etc...When you open the linked spreadsheet in a direct linked table this field shows records with the error #NUM! possibly because during the field conversion Access assumes that it's a number field dropping the all text. I tried converting the entire column to text in Excel and now it gives me an error on the all number fields. MLE #NUM! SSM #NUM! I don't see any other format in Excel that would accomodate this circumstance. Please help...thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Excel as an actively linked workbook in Access | Excel Worksheet Functions | |||
Excel File Linked in Access | Excel Discussion (Misc queries) | |||
Excel to Access linked tables | Excel Discussion (Misc queries) | |||
New Excel data does not appear in linked Access table | Excel Discussion (Misc queries) | |||
Excel linked to Access | Excel Programming |