Linking excel tables in access database

[DebS]

The table in excel has id codes (numbers), that are
formatted as text. When linking the excel table in access
it converts the field to number format. Number format is
not compatible with other tables in access for which I
need to link and query together. I have tried adding
records in the excel table using text verses numbers, but
access still changes the field to numeric, erroring the
record with the text. There does not seem to be any way
during the linking process to designate the field format.
Can anyone help me?

 

[John Nurick]

Hi Debs,

Linking Excel data to Access is just plain weird. It gives you no
control over the fieldt types Access assigns, and the rules it applies
in doing so are - for no apparent reason - different from the rules it
applies when linking. According to
http://support.microsoft.com/default.aspx?scid=kb;en-us;208414
if there is a single numeric value in the first 8 rows Access will
assume it's a numeric field even if the other 7 rows contain text
values. Whether the numbers are formatted as text seems to make no
difference.

So if your table is linking successfully, count yourself lucky even if
the field types aren't what you want.

One approach to force your "textual" numbers to import as such is to
insert an apostrophe at the beginning of each, e.g.
500 becomes '500
.. This is an old way of indicating that what looks like a number in a
spreadsheet is in fact text. The apostrophes are not displayed in Excel
cells and do not form part of the data in the linked tables, but they do
force the column to treat them as text.

If you can't modify the Excel data in this way, let the id code field be
imported as a number. Then use a query to convert it to text, and join
the query to the other tables as needed.

The text conversion would be done wth a calculated field something like
this:
ID: Format([ID_Numeric], "000000"

The table in excel has id codes (numbers), that are
formatted as text. When linking the excel table in access
it converts the field to number format. Number format is
not compatible with other tables in access for which I
need to link and query together. I have tried adding
records in the excel table using text verses numbers, but
access still changes the field to numeric, erroring the
record with the text. There does not seem to be any way
during the linking process to designate the field format.
Can anyone help me?

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.