Exporting a fixed-width file

[Guest]

I need to export a fixed-width file, ascii format, with zero-padded numeric
fields and space padded text fields. I currently have my information in a
table. My numeric fields look corrent in my table field, but when I export,
I lose the zeros. Any help would be appreciated.

 

[Douglas J. Steele]

Create a query that uses the Format function on your fields (as opposed to
simply formatting the field itself). Export the query, not the table.

 

[Guest]

I don't know what you mean by Format function, a little more guidance would
help.

 

[Douglas J. Steele]

Assume that you want, say, a 9 digit value, so that 1234 would appear as
000001234, you'd create a query that has SELECT Format([MyField],
"000000000") FROM MyTable.

To do this in the query building, drag the field into the grid as normal, so
that you have MyField in a cell, then replace that with Format([MyField],
"000000000"). If it's important that you have field names rather than the
Expr1 the resultant field will be called, you can put a field name, then a
colon, in front of what you typed, so that you have

FieldName: Format([MyField], "000000000")

Unfortunately, you cannot name the field MyField, as that would create a
circular reference.

 

[Guest]

Thank you very very much!!!!!

Assume that you want, say, a 9 digit value, so that 1234 would appear as
000001234, you'd create a query that has SELECT Format([MyField],
"000000000") FROM MyTable.

To do this in the query building, drag the field into the grid as normal, so
that you have MyField in a cell, then replace that with Format([MyField],
"000000000"). If it's important that you have field names rather than the
Expr1 the resultant field will be called, you can put a field name, then a
colon, in front of what you typed, so that you have

FieldName: Format([MyField], "000000000")

Unfortunately, you cannot name the field MyField, as that would create a
circular reference.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)

I don't know what you mean by Format function, a little more guidance would
help.

 

[ajvasavada]

Works great but it creates a file delimited with | and records ar
separated by --. How do I remove these?.

Thanks.

Ashok

*Thank you very very much!!!!!

:
[vbcol=seagreen]

Assume that you want, say, a 9 digit value, so that 1234 woul appear as
000001234, you'd create a query that has SELECT Format([MyField],
"000000000") FROM MyTable.

To do this in the query building, drag the field into the grid a normal, so
that you have MyField in a cell, then replace that wit Format([MyField],
"000000000"). If it's important that you have field names rathe than the
Expr1 the resultant field will be called, you can put a field name then a
colon, in front of what you typed, so that you have

FieldName: Format([MyField], "000000000")

Unfortunately, you cannot name the field MyField, as that woul create a
circular reference.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


would
to
in a


[/vbcol]

-
ajvasavad

 

[Douglas J. Steele]

How are you exporting the data? Try using the TransferText command.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Works great but it creates a file delimited with | and records are
separated by --. How do I remove these?.

Thanks.

Ashok

*Thank you very very much!!!!!

:
[vbcol=seagreen]

Assume that you want, say, a 9 digit value, so that 1234 would appear as
000001234, you'd create a query that has SELECT Format([MyField],
"000000000") FROM MyTable.

To do this in the query building, drag the field into the grid as normal, so
that you have MyField in a cell, then replace that with Format([MyField],
"000000000"). If it's important that you have field names rather than the
Expr1 the resultant field will be called, you can put a field name, then a
colon, in front of what you typed, so that you have

FieldName: Format([MyField], "000000000")

Unfortunately, you cannot name the field MyField, as that would create a
circular reference.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


would
to
in a


[/vbcol] *