VBA Access Delete Number 0 on import 1

[Mrogersy]

I am trying to import a file into a table that has the field set as a text field. It is importing the number 00026422 as this, and I need to remove any preceeding 0's before import to read 264222.
Anybody know how to do this?

 

[SkipVought]

Hi,

Leading ZEROS are significant for TEXT fields, while they are meaningless for NUMEIC fields.

If it is a TEXT field, then you must replace the leading zeros with SOMETHING. What would that SOMETHING be? SPACES? Otherwize, you might have a situation like this
[tt]
00000003
00026422
[/tt]
result in
[tt]
3
26422
[/tt]
which will colate as
[tt]
26422
3
[/tt]
Unless you don't really care.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Mrogersy]

I do not care, and would not like anything there (not even spaces). AFter I import the file, I am matching that number to another table which shows the number as 264522, instead of 264522, as it is being imported. My vendor sends the data two different ways, 264522 and 000264522.
Thanks,
Mike

 

[lameid]

Skip has a point but the exporting source may just 0 fill numbers left. If that is your case...

If you are importing into Access which your subject implies and it really is a numeric field, change the datatype in your table to a number of an appropriate type.

Otherwise you can convert it to a number using a function and update to this value after import.

Cint, cdbl, clong and Val are all functions that you may consider using to convert to a number.

 

[SkipVought]

My vendor sends the data two different ways, 264522 and 000264522.

Keep the leading ZEROS.

Use the Format function to PAD with ZEROS when they are missing, as the ID is ACTUALLY a 9 byte STRING.

Format(YourRef,"000000000")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lameid]

I would ask the vendor if it is a numeric field. If it is, fine they zero fill left on some exports and then you can get rid of the zero's. If it is a text field, I would insist that they include the zero's on both transmission of data.

 

[Mrogersy]

Thanks, I might go that route instead.

 

[SkipVought]

In many instances, IDENTIFIERS contain all numeric characters, but they are NOT really NUMBERS with which you would ever do arithmetic.

Part Numbers, Invoice Numbers, Phone Numbers, SSNs, US 5-Digit ZIP Codes, are ALL examples of IDENTIFIERS that of do contain ONLY numeric characters.

The tip-offs:
[tt]
Leading ZEROS are significant
Length of the value is constant
Not used for math
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}