text and numbers in linked excel spreadsheet

[Guest]

I'm trying to do a query on a linked excel spreadsheet. This spreadsheet has
a column that contains some text entries and some numerical entries. I have
sorted the spreadsheet so that Access recognizes the column as a text field.
I have also defined the column in Excel as a text field.

When I query the spreadsheet, it is recognizing a few of the numerical
entries as text, but the rest of the numerical entries show up in the query
results as #NUM!

What am I missing? I can't figure out why some of the numerical entries are
recognized and some are not.

Thanks!

 

[Ron2006]

I'm trying to do a query on a linked excel spreadsheet. This spreadsheet has
a column that contains some text entries and some numerical entries. I have
sorted the spreadsheet so that Access recognizes the column as a text field.
I have also defined the column in Excel as a text field.

When I query the spreadsheet, it is recognizing a few of the numerical
entries as text, but the rest of the numerical entries show up in the query
results as #NUM!

What am I missing? I can't figure out why some of the numerical entries are
recognized and some are not.

Thanks!

I look forward to seeing the answer to this one also.

I have had the same problem and have simply given up link to excell
and in all cases I Import into a table with significantly better and
consistent results.

Ron

 

[Guest]

I was able to temporarily solve the problem by copying the column, Paste
Special (text) into a Word doc.

Then deleting the column in Excel, inserting a new column in its place,
formatting the entire column to text.

Then copy the text from Word and Paste Special (text) back into the new
column in Excel. However, it remains to be seen whether it will continue to
work when I enter new rows in the spreadsheet.

I would certainly be interested in a better solution, if anyone knows of
one...

Thanks!

 

[Guest]

You've done everything that MS says to do to fix the problem.. certainly it
must work! /Sarcasm

Excel has always had issues with number types, and nothing has changed.
While I'm sure there's probably an easier way to deal with the problem, what
I've done in the past is create a formula in excel which forces a text value
from the cell.

Something like ="" & A2.. the link to that column will show all values as
strings.