Loosing Preceeding 0's importing field

[kschomer]

I have set up an application for ordering literature.  The ordering is automated in the field via telephone.  The orders are downloaded from PROCOM into a *.txt file or *.xls format.  The user runs queries to delete old data and then imports new data.  The PROCOM system automatically downloads the Part number with a single quote in front.  (ie '00001, '00831 and other odd ball part numbers like '0178, 0378, 0424, 00127-1 and AD0427).  However, in the Access table the user is appending the data into the field PART#; Text; field size 10) is truncating the actual part number so that the value is 001 instead of '00001 etc.  Since I am not a programmer I am not doing anything too fancy.  I am using an update query mid([TBLNAME]![PART#],2,"9&quot But, that's not the write solution.  Have any ideas? Can you help?

 

[kathryn]

I'm sorry, but I am not sure what you want.&nbsp;&nbsp;Do you want to force the entire part number to get imported?&nbsp;&nbsp;In reading your question, I think you lost part of a sentence.<br><br>If you want to force your field to be 10 characters long with preceding zeros, I don't know how to do it, since you are dealing with text and leading zeros can only be forced for numeric fields. <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[kschomer]

The field happens to be 10 characters, but the part numbers are only 6 characters.&nbsp;&nbsp;I don't want to force 10 characters I just want the part numbers to appear in my Access table the way they appear in the text file which was downloaded from PROCOM.&nbsp;&nbsp;For example part number '00001 downloads okay but in Access is appears as 001 for some reason the '00 is getting lost.&nbsp;&nbsp;STRANGE???

 

[Paul7905]

I had the same problem importing Excel spreadsheets into an Access table, was losing leading zeroes ~&nbsp;&nbsp;I can't recall exactly how I fixed it but the problem was related to the cells in the spreadsheet being defined as numeric vs. text format or visa versa~&nbsp;&nbsp;&nbsp;(one format will cause the zeroes to truncate where the other will not)&nbsp;&nbsp;&nbsp;to fix the problem I had to redefine the format of the cells in Excel (i was doing a direct import of the spreadsheet into an access table and did not use a query). <br><br>Paul

 

[THoey]

If it is a text field, you could get the length of it and&nbsp;&nbsp;append the appropriate number of 0's to the front.&nbsp;&nbsp;&nbsp;<br><br>intField_Length = LEN(txtfield)<br>if intField_Length &lt;&gt; 10 then<br>&nbsp;&nbsp;&nbsp;for i = 1 to (10 - intField_Length)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;txtfield = &quot;0&quot; & txtfield<br>&nbsp;&nbsp;&nbsp;next i<br>endif<br><br>Before doing this, you would want to make sure that the truncation is just dropping 0's not other values...<br><br>I will say I think it would be better to find the reason for the truncation instead of using this band-aid... <p>Terry M. Hoey<br><a href=mailto:th3856@txmail.sbc.com>th3856@txmail.sbc.com</a><br><a href= > </a><br>