Need a leading zero 3

[vig]

I am exporting a query in a external fixed width text file.&nbsp;&nbsp;One of the fields is a 4 digit numeric. How do I get leading zeros included so the number is right aligned. How do I setup the property for this numeric in the table.<br>Not <br>123<br>2<br>23<br>but<br>0123<br>0002<br>0023<br>Thanks for your help, again.

 

[elizabeth]

You can concatenate it into a text field with a string function in an update query:<br>Expr1: &quot;0&quot; & [yourfieldname]<br>

 

[Cornerstone]

Since the number of zeros changes depending on the number I would do the following. Make the field a text field.<br><br>Then run this procedure on it:<br><br>Sub AddZeros()<br><br>dim db as database<br>dim rst as recordset<br><br>set db=currentdb<br>set rst=db.openrecordset(&quot;yourtable&quot<br><br>rst.movefirst<br>do until rst.eof<br>&nbsp;do until len(rst!yourfield)=4<br>&nbsp;&nbsp;rst.edit<br>&nbsp;&nbsp;rst!yourfield=&quot;0&quot; & rst!yourfield <br>&nbsp;&nbsp;rst.update<br>&nbsp;loop<br>&nbsp;rst.movenext<br>loop<br><br>End Sub

 

[elizabeth]

Oops I didn't notice about the varying number of zeros. To do this with built in functions, do something like this, substituting your max # of leading zeros for &quot;0000&quot; and your total desired field length for 4:<br><br>Right((&quot;0000&quot; & [yourfieldname]),4))

 

[vig]

Elizabeth--Where do I place the expression?--in the Update Query as you mentioned before.<br>Thanks.

 

[elizabeth]

Just key it into the &quot;Field&quot; row, in it's own column. The part before the colon &quot;:&quot; is your column heading, you can change it. Expr1 is the Access default if you don't key in a title.

 

[vig]

Elizabeth--<br>(The good news is) I almost got it to work.&nbsp;&nbsp;The leading zeros showed up throughout the external fixed width text file export...but (the bad news is) the zeros did not follow nor was it left justified in the actual .txt file. Any ideas?&nbsp;&nbsp;Thanks for your help.

 

[elizabeth]

Don't understand what you're saying: <br>1. 'the zeros did not follow&quot; ?<br>2. 'nor was it left justified in the .txt file' ? Please try to be clear.<br><br>Did your values come out<br>0123<br>0002<br>0023<br>?<br><br>If the field length is 4 and the values are each 4 characters long, I don't see how they can fail to be left <i>and</i> right justified...

 

[vig]

Let me try to clarify--The values came out just as shown above by you but when I exported it to an external fixed width text file, even though the zeros showed throughout the wizard, in the text file that was created the zeros were gone and the numbers reverted to <br>123<br>2<br>23<br>and not lined up right justified.&nbsp;&nbsp;Thanks.<br>

 

[Cornerstone]

Use my solution if you want it to work the way you want. See above.

 

[elizabeth]

Hmmm... curious... Did you create a table with a text field of length 4 to put this into, and after you ran the update query, were all your fields 4 digits in the table? I'm trying to figure out where in the process it went wrong, if the table was OK <i>before</i> the export, but it exported differently. Cornerstone, have you tried this method and it doesn't work?

 

[Cornerstone]

Honestly I did not try your method. I just know that the method I posted does work. Yours may work as well I will try it when I can.

 

[vig]

I originally created the field as a numeric so I could make the format 0000.&nbsp;&nbsp;Of course this didn't work in export.&nbsp;&nbsp;and the new field in the query didn't translate to the .txt file though it looked throughout as it would. I was afraid to change it to a text field since it was a primary field and a lot of relationships, fill-ins and other properties depended on this...but today I worked on this change.&nbsp;&nbsp;&nbsp;I changed every table with this numeric field to a text field after removing the relationships.&nbsp;&nbsp;I replaced the relationships. I added @@@@ to the format line, I backfilled the field. I am still testing but initially I am having success with the Export.&nbsp;&nbsp;Don't know if the rest will work. Thanks guys for the mental challange, as usual. I am not a programmer, as yet, so I tried to work within the Access program.&nbsp;&nbsp;Cornerstone, does your routine backfill?&nbsp;&nbsp;