Excel linked to Access

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.


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.
 
Back
Top