Importing or Appending data with TEXT to NUMBER conversion

[Guest]

I am trying to load some test data using ACCESS that I created in Excel that
contains leading zeros on some records into a new data table. When I import
this data into ACCESS, the data type automatically configures to TEXT (Excel
data is text, to allow leading zeros... otherwise NUMBER format truncates
leading zeros), without allowing and change. All values are "numeric" text
(no alpha characters)...

I am then appending this table of data to an existing ODBC table, in an
effort to simulate loading of this interface table for testing purposes.

Can anyone provide me any advice on how I might resolve this issue?

 

[Guest]

Further information... samples of data values I am working with...

00007420105
00009347503
00029152725
00173026010
50242013468
58178001703

 

[John Nurick]

Mark,

Are these things numbers (in which case leading zeroes are not
significant and Access won't preserve them either), or strings of digits
(in which case they should be stored in a text field)?

Values such as 58178001703 are too big to store in a standard Access
number field (i.e. a Long), so if these really are numbers you would
have to use a Currency or Decimal field.

 

[Guest]

These values are significant -- the zeros are part of the identifier, so
numeric (or Double) doesn't seem to work, because it drops the leading zeroes.

I believe that I will be stuck having to manage these values as Text, unless
I can figure out how to retain the leading zeros.

 

[John Nurick]

Basically, if you're going to do arithmetic on them you need to treat
them as numbers - in which case the leading zeroes are *not* significant
and 14, 0014, 0E and XIV are the same number differently expressed). You
can always supply leading zeroes for display purposes by using an
expression like
Format(MyNumber,"00000000")

If arithmetic isn't involved, they are just strings of digits and you
should store them in text fields (where "14", "0014" and "0E" and "XIV"
are distinct).