Leading zero's are dropped during export

[ggraham]

Help!! I know this is a very simple request, but I can't seem to solve the problem in access. I have created a table that has the following fields: last name, first name, ss#, and wages. The wages field is a numberic field that in 9 characters long with zero's padding the field (ex: 000056023) = $560.23. When I export, the zero's are dropped. Any advise would be greatly appreciated.

 

[thebowler]

You did not state where it was being exported to. If you are exporting to Excel, Excel always drops off the leading zero's. What you can try is putting an apostrophe at the beginning of the value before exporting.

 

[ggraham]

I need to export it to a file that will be a fixed length of 302 positions. I will then use this to transmit data to our state retirement system. It's a new requirement put on us by the state and of course, they tell us that it's up to us to figure out...they provided no guidance on it.

Thanks for any information

 

[Imhoteb]

Hi,
Do you need the field to be numeric in your table?
If not change it to text. The leading zero's will not be dropped then. Before you store the value, you can always check to see if it is numeric. But storing it as a textfield should solve your problem

 

[rgomez1999]

Hi, I had that problem with ID numbers that are assigned to patients in my medical facility and what I did was create an update query to fix that leading zero problem. Here's how it looks in SQL view:

UPDATE [PatientData] SET [PatientData].IDNO = Format([IDNO],"0000"

in the update to section of the update query it's like this:

Update to: Format([IDNO],"0000&quot

That's because my clients are given a 6 digit number and when I did an import a few zero's were missing.

Hope this helps.

 

[ggraham]

Thank you RGOMEZ1999. That worked great. Thanks so much.