Droping Leading Zeros In Table


B

bwottle

Hi,

I really need some help with a large amount of data that I'm importing
from Excel to Access. The data field that I'm having trouble with is a
text field or general filed in Excel that was created from a mainframe
application. The field has 7 character, they are numbers, all with a
leading zero.
Example

0134251
0783421

When I import this data into an Access table, I loose the leading
zeros. I've tried formatting the table as text, tried setting the
input mask like this 0000000 or 0####### and some others. Nothing I'm
doing will return the leading zeros. Is there a way that I can perform
this from within Access? If it matters I'm using Access 2007.

Thanks,
Brian
 
Ad

Advertisements

K

Krzysztof Pozorek [MVP]

(...)
0134251
0783421

When I import this data into an Access table, I loose the leading
zeros. I've tried formatting the table as text, tried setting the
(...)

Change in the first cell from 0134251 to '0134251.

0134251 -> '0134251

Then import Excel document again.

K.P.
 
C

Clifford Bass

Hi Brian,

Or, since the zero provides no additional information, you can import
them as numbers (long) and when you print them you can use 0000000 for the
format (not the input mask) or you can use the Format() function:
Format([MyField], "0000000").

Clifford Bass
 
B

bwottle

(...)> 0134251


(...)

Change in the first cell from 0134251 to '0134251.

0134251 -> '0134251

Then import Excel document again.

K.P.

I no know about the '0123456, but there are several thousand records
in Excel. I haven't been able to figure out how to automate this. I
tried to write a macro to do this, but it wouldn't go to the next cell
when I ran it.
 
Ad

Advertisements

C

Clifford Bass

Hi,

I presume the numbers are coming in as long integers. Have you tried
my suggestion of setting the Format property of the field to 0000000? Note
that the Format property is different from the Input Mask property.

Clifford Bass
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top