#Num!

[Salad]

If I link to an Excel spreadsheet, some values might be mixed. Ex:
Phone
500-123-4567
5001234567

The value in the second row displays #Num!

Is there a way to extract the value of that field? It's not text (tho
the column says it's text) and I can't seem to do a CStr(FieldName)

Is there someway to format and entire spreadsheet to be text for all
values? Or someway so the mix/match won't cause problems?

[CDMAP...@fortunejames.com]

Using information from:

http://groups.google.com/group/microsoft.public.access/msg/c50fa65815f3fe38

and

http://www.dicks-blog.com/archives/2004/06/03/external-data-mixed-data-types/

I created the following spreadsheet:

Book1
Sheet1

F1 G1
2 3
Hi Bye

When I link to the spreadsheet from Access in the normal way I get:

F1 F2
---------
F1 G1
#Num! #Num!
Hi Bye

but the following SQL:

SELECT * FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=No;IMEX=1].
[Sheet1$];

gives:

F1 F2
---------
F1 G1
2 3
Hi Bye

and

SELECT * FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=Yes;IMEX=1].
[Sheet1$];

gives:

F1 G1
---------
2 3
Hi Bye

and

SELECT * FROM [Excel 8.0;DATABASE=C:\Book1.xls;HDR=Yes;IMEX=0].
[Sheet1$];

gives:

F1 G1
---------
2 3
#Num! #Num!

but my Linked Table Manager shows the connection to be simply C:
\Book1.xls\Sheet1$

So my guess is that you need to be able to get at the connection
string so that you can specify the IMEX value or you need to change
the registry.

James A. Fortune
CDMAP...@FortuneJames.com

[Salad]

CDMAP...@FortuneJames.com wrote:

Excellent response, James.