0 is missing after export to text file

[cluser]

I have an Access table with a field named lunchpin, it is 5 digit number, but when I export to a text file, it turns to be 4 if it has a 0 in front of it, for example 02119, becomes 2119, how can I keep the number still 5 digits, without losing the leading or ending 0s.

Thanks

 

[PCLewis]

Sorry, I do not believe that you have a field in an access database that is a number field that has whole numbers in it starting with a 0. You either have a display format like "00000" which pads it for you for display purposes only OR you have a text field. If the latter then your text file will contain the leading 0 but the applicaiton you are using to view the data is dropping it (common if importing to Excel).

I suspect the former is the case where the field is a number field but the display format has given you the false impression that exporting the table will keep the leading 0.

What you will need to do is write a query, translating the number to a string, e.g. Format(myField,"00000") as myField2

The export the query to your text file and you will have the leading 0.

 

[dhookom]

cluser,
Is the data type of the field text or numeric? If it is numeric, consider exporting a query that formats your numeric field like
Lunch_Pin: Format(Lunchpin,"00000")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[cluser]

Thank you very much

 

[SeeThru]

Oh, if you export it as CSV or XLS and open it in excel, it often strip off leading 0's as it decides the field should be numeric and not text.