Linking numbers as text

[Snoop]

Here's an easy one...

I have XL tables with many fields, some of which are all
numbers. However, the numbers are more like IDs, so I
want them linked in Access as text.

1. Access automatically links them as 'number' data type.
2. Ensuring that they are formated as 'text' in XL makes
no difference.

There must be an easy solution, but it has not been easy
to find!

 

[John Nurick]

Hi Snoop,

The best way I know to do this is to enter them in Excel with an
apostrophe in front, e.g.
'12345
The apostrophe forces Excel and Access to treat the numbers as text, but
does not show up in either the worksheet or the linked table.

To add or remove apostrophes to/from existing Excel data you can use
these little Excel VBA functions:

Sub AddApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
If IsNumeric(C.Formula) Then
C.Formula = "'" & C.Formula
End If
Next
End Sub

Sub RemoveApostrophes()
Dim C As Excel.Range
For Each C In Application.Selection.Cells
C.Formula = C.Formula
Next
End Sub

 

[Rolls]

An alternative is to use Access datatype or string functions to test each
field then change it to the datatype you need. Linked or imported data can
present several problems such as being Null or the wrong Type. It's good to
know how to a) test for a condition and b) change it.

See Help for these functions:

IsNumeric()
IsNull()
IIF statement