Fixed Length export text file is left justifying my numbers

[Tony1l]

Here is a short description of my task: I am converting data into a format for other companies and many times I'm required to give them a flat file in fixed width format with numbers right justified.

Example:
25 After-> 000025
I convert the number by setting the data type to number and the format to 000000.
When I run the Export and choose file type: Text File> Export ALL>Fixed Width.. my numbers have their zeros stripped away and are now left justified.

FYI: To work around this problem I've been using Excel and setting the field format to 000000 and exporting a text file. It correctly leaves the zero's, but now I have to run the file through a text converter (UltraEdit) and change the file to a fixed width. I would prefer to only use Access to generate the file.

Last note, I found some information about this on Microsoft's support web page but the solution they have is to set an Import/Export Specification script. I've made several attempts to create a Export specification but have failed. I'm still too much a newbie with Access. I would appreciate any tips or information you could lend.

 

[SkipVought]

Tony,

A number is whatever it is, regardless of the formatting. For instance, if you have a number to 5 decimal places and you format it to 2 places, it's STILL FIVE PLACES.

Likewise, leading zeros are ONLY a FORMATTING DISPLAY feature. 0000000000001 is still 1.

What you need to do is write a query to format your numbers as TEXT with leading zeros.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blown-apart!}

 

[Tony1l]

A 3rd party requires the format to be the same number of characters for every number with left padded zeros.

I appreciate your suggestion. I did set the datatype to text and used a format of 000000 but again it loses the heading zeros and leaves only the value. I thought that there may be an option I was overlooking, but if it requires some update query to change numbers to text I'm afraid I'm too new to SQL and VBA to write such a phrase. Could you post an example of a statement and I'll try to implement it.

 

[SkipVought]

Select ...., Right("00000000"&[Yourfield], 8)

is one way to pad leading zeros to 8 digits.

Another way would be

Select ...., Format([Yourfield], "00000000")

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blown-apart!}

 

[VBslammer]

I convert the number by setting the data type to number and the format to 000000.

What you need to consider is that [tt]000025[/tt] is text, not a number. Using a conversion method such as [tt]Format(25, "000000")[/tt] simply converts the number to a string with the desired appearance - which is exactly what needs to happen for the export to a flat text file.

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[Tony1l]

I welcome all of the help. I was finally able to get this to export correctly after converting my number to text using the earlier described method.
My Table was called (Items) and my field was (cost).
I ran a query in design view and changed my view to SQL view.
The key for me was to type:
update items set items.cost = right("000000"&[cost], 6)

I then updated other fields which then correctly exported with left padded zeros!
I would have never done it without your line Skip.

Thanks,
Tony