If you're linking to a non-Access table (Excel spreadsheet, text file,
etc), the problem is that the column contains both numbers and
non-numbers. When you link in Access, it analyses the first few values
in each column and determines the data type from them. In your case,
the first few values are numbers, so Access sets the data type to
"Number". Later, when it reads the non-numbers, it displays "#Num!"
If your column SEEMS to contain all numbers, it may be that some of
them have been entered as text, and are causing the error. There are
many reasons that this may happen, which I won't go into here.
Formatting the column to Text AFTER the data has been entered makes no
difference - it'd only work if the formatting was set before the data
entry.
You will need to create the following macro in Excel, which will force
numbers to be treated as text values:
Public Sub SetNumbersToText()
Dim rng As Range
For Each rng In Intersect(ActiveSheet.UsedRange, Selection).Cells
With rng
.NumberFormat = "@"
.Value = " " & .Value
.Value = Mid(.Value, 2)
End With
Next
End Sub
Select the column that is giving you trouble, then run this macro. All
cells will be formatted as Text, and all numbers will be re-entered as
text values. Re-open your database, and the linked table should behave
itself. The troublesome column should now be set to "Text" data type.
Alternatively, if your column is supposed to contain numbers, and some
of them have already been entered as text, try this macro:
Public Sub SetTextToNumbers()
Dim rng As Range
For Each rng In Intersect(ActiveSheet.UsedRange, Selection).Cells
With rng
.NumberFormat = "General"
If IsNumeric(.Value) Then
.Value = Val(.Value)
End If
End With
Next
End Sub
The "#NUM!" values should disappear.
If you're linking to something like a text file, open it as an Excel
spreadsheet, and run the above macro(s).
I hope this helps.
Mark.
