transfertext command is dropping leading 0's how to stop?

[danny]

I am using access to create an inventory compiler for my wine shop.

Many UPC's have leading zeros. All of the scanners we use capture those
zeros. When I import the text file from my handheld scanners using the
TransferText command, it drops the leading zero(s) and I can no longer match
against my other tables that have that information.

I have a work-around but it is cumbersome. Anybody know how to prevent this?

 

[Dale Fye]

Danny,

How are you importing? Are you importing into an existing table? If so,
your UPC field should be text. But Access will probably try to interpret
the UPC values as numbers, and drop the leading characters. Try creating an
import specification, and setting the UPC field to text in the import spec.

To create an import specification, select the File -> Get External Data ->
Import. Once you have identified the file you want to import from, you
should see an "Advanced" command button on the lower left corner of the
import wizard. Click this. It will bring up a list of the fields in the
file to be imported, and will allow you to change the datatype associated
with that field. Change your UPC field to Text, and the other fields to the
appropriate data types. Make sure these data types jive with what you have
in the table you are importing to.

HTH
Dale

 

[gllincoln]

Hi Danny,

Your handheld scanner file - could you post a few rows of data so we know
what kind of file you importing? Your description of what you are doing
makes it sound as if you will be doing this scanning and importing process a
lot. Be good to get it automated and reasonably bulletproof. If you have a
consistent file format that you will be importing a lot, it's often more
efficient, reliable to create a custom import function that 'knows' what
it's looking for, validates the data, does any formatting adjustments
required, and inserts it where needed.

Are you importing the file as a new table? Is it automatically creating
that column/field as a numeric type? Long probably?

If so, change the table (design view, table, find your UPC column, change it
to text with a length as long as you think you will ever need for a UPC
code.)

If you import your text file into an existing table, as long as the columns
in the text file are in the same ordinal (left to right) order as the table
and you have the same number of total fields/columns then this may resolve
the problem.

You have a fixed length UPC, is this correct? If you know how many
characters you are expecting the UPC to provide, then as a bandaid, you can
fix the column that is broken using an update query with contrived padding
function.

Assuming the name of the field is UPC, using the dummy table name TABLE and
the length you want is 15 - change the names and numbers to fit your needs.

UPDATE TABLE SET UPC = RIGHT(String("0", 15) &

.[UPC], 15);

Using a length of 3 to explain what this does...

Assuming you start out with 001 and it gets truncated to 1.

string("0",3) will return 000 and your file currently contains a 1.

Concatenating them (jamming them together into one string of text) gives you
0001. Now if we grab 3 chars counting from the right, we are back to 001.

if we had started with 011 and it went to 11. we build 00011, then grab the
right hand 3, we get 011.

If this seems obvious to you - I apologize for beating it into the ground.
The key here is you have to know how many characters you are expecting to
see.

Hope this helps,
Gordon