Access Fixed width export

[Tarquin]

I am trying to export a table in fixed width format.

This works fine, to a point, the wizard proceeds all the way through and the data looks good until I check the resulting output file and I find that some fields are being treated as numeric and a decimal point inserted.
This is frustrating because in the access table I'm exporting ALL fields are text.
The fields at issue contain digits with leading zeros and the field formats in the table are set "0000", "00000000" etc.

I'm using Access 2002

 

[PHV]

Create a query using the Format function to get the presentation you want, and then export this query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[payback]

Have a look at the Microsoft Knowledgebase article 208408 - it might address your issue.

 

[Tarquin]

Thanks alot for these suggestions unfortunately . . .

PHV - I get the same when I export the query, looks fine in the wizard but the output file has decimal point and places inserted. The text fields containing digit strings have been converted to numeric with 2 decimal places.

Payback - The microsoft solutions, the 'save formatted' gives me an output file with | and - characters producing a grid the alternative I've already covered by setting the field type to TEXT.

What I'm trying to get is a fixed width text file so that my access data can be simply loaded into a different external database.

Any further suggestions gratefully received.

 

[PHV]

And what about a query with a single formatted column ?
SELECT Format(Field1, '0000') & ' ' & Format(Field2, '00000000') & ....

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[payback]

What are your VB skills like? I could probably offer a solution using VB that I have used before.

 

[Tarquin]

Thanks guys.

PHV - I see what you mean now, I'll try it.

payback - VB solution is interesting, if it gave some of the functionality of awk it would be good.

I guess the microsoft wizard pre-release testing didn't include checking the output!!