RE: #NUM error on linked table

[Barry Gilbert]

Access tries, unsuccessfully in your case' to figure out the data type of the
range in Excel based on the value in the first cell of each column. The first
value in your column in Excel is probably numeric, so Access designs the
column in the tyable as a number. Here's the fix (I hope):

In Excel, highlight the column and change its type to Text (Ctrl+1). Change
the value in the first value cell to someting with a text character. Go back
to Access and re-link the table. Access should now set the field to a Text
type. You can now go back to Excel and restore the value that you changed.

Barry

"Mominator" wrote:

> Help.... I have an excel sheet I want to link to...but when I do, i get the
> #NUM error
> on over half of the records....(ones which have both a number and a letter
> in the cell) but when I import the same excel worksheet, everything is
> fine....
> how can I link but not have the error?
> any help is greatly appreciated!

[Mominator]

Hi Barry,
Thanks for your help...you are correct in Access setting the field property
to "NUMBER" but changing the first field did not work....the value in the
first cell is:
124-0931A5
I tried changing it to all text (ALLTEXT) and then linking, but still got
the error on all the cells that have a number with a text character. Of
course Access won't let me change the properties of the field on a linked
table....so what can I do now? I can't change all the product number codes.
Side point....if I "import" the table I do not get the error... I only get
it when I link.
Thanks for any help you can give!

[Barry Gilbert]

After making the change to the formats and first cell in Excel, you need to
drop the linked table in Access and re-link it. This is the only way Access
will change the table properties.

Barry

[Mominator]

Ok, I must be doing something wrong...please instruct on the correct way to
do it..
I changed the first two cells in the column to words and even went as far as
formatting to text in excel. opened a new dba and linked to that excel
sheet.
same thing....every cell that has a letter mixed in with the number comes up
with the #NUM error...
What am I doing wrong?
Thank you for your patience!

[Barry Gilbert]

This has to do with the way Excel stores values. You can correct individual
cells by putting a space in front of the cell's value, hitting enter, and
then remove the space. You can also create a small macro in Excel that will
do this to a range of selected cells.

Open the Excel macro editor (ALT + F11)
Create a new module by right-clicking the module node in the Project
Explorer (CTRL+R if it's not visible) and selecting Insert Module

Paste the following code into the new module:

Public Sub FormatAsText()
Dim cell As Object
For Each cell In Selection
cell.Value = " " & cell.Value
cell.Value = Right(cell.Value, Len(cell.Value) - 1)
Next
End Sub

Back in your Excel workbook, you can run this macro by selecting the cells
you want to fix (a whole column?) and selecting Tools, Macro, Macros
(ALT+F8). Find the FormatAsText macro you just created, highight it, and
click run.

Go back to Access and re-link the table.

Kind of a long way around, but it's necessary if you have a mixture of
formats within a column.

Barry

[John Nurick]

Apostrophes work better than spaces, IMO.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.