I've been trying to export a fixed width text file with some fields that
contain blank values. When I specify the start position and length of the
fields, Access 2007 won't export the blank fields with the correct number of
spaces I need. Each record/row must be a certain width (224 characters).
When I put in a length of 28 for one of my blank fields, it only puts 11
spaces in that field -- I've tried increasing the length, but still only get
11 spaces in the .txt file. What am I doing wrong? Thanks in advance!

--
Jen Scott

"Jen Scott" <(E-Mail Removed)> schrieb im Newsbeitrag
news:191654A8-5FAB-4361-8248-(E-Mail Removed)...
> I've been trying to export a fixed width text file with some fields that
> contain blank values. When I specify the start position and length of the
> fields, Access 2007 won't export the blank fields with the correct number
> of
> spaces I need. Each record/row must be a certain width (224 characters).
> When I put in a length of 28 for one of my blank fields, it only puts 11
> spaces in that field -- I've tried increasing the length, but still only
> get
> 11 spaces in the .txt file. What am I doing wrong? Thanks in advance!
>
> --
> Jen Scott

On Fri, 3 Apr 2009 13:28:01 -0700, Jen Scott
<(E-Mail Removed)> wrote:

>I've been trying to export a fixed width text file with some fields that
>contain blank values. When I specify the start position and length of the
>fields, Access 2007 won't export the blank fields with the correct number of
>spaces I need. Each record/row must be a certain width (224 characters).
>When I put in a length of 28 for one of my blank fields, it only puts 11
>spaces in that field -- I've tried increasing the length, but still only get
>11 spaces in the .txt file. What am I doing wrong? Thanks in advance!

A Table field trims trailing blanks. Try exporting from a Query rather than
from the table. In 2003 and earlier you can create an Export Specification, I
don't have 2007 installed so I'm not sure how or whether to do so there; but
you can put in a calculated field

ExpField1: [Field1] & String(" ", 27 - Len([Field1]))

to tack the needed number of blanks on to the field value.
--

John W. Vinson [MVP]

When I put this into my query it runs, but returns "Error#" in the field
name. I think this might work when there are values in the fields, but my
field is blank and I need to export a certain # of blank spaces (16 to be
exact) before the first value in the fixed width text file. If I try to
export it with the function, it gives me another error and won't export.
Thanks!
--
Jen Scott


"John W. Vinson" wrote:

> On Fri, 3 Apr 2009 13:28:01 -0700, Jen Scott
> <(E-Mail Removed)> wrote:
>
> >I've been trying to export a fixed width text file with some fields that
> >contain blank values. When I specify the start position and length of the
> >fields, Access 2007 won't export the blank fields with the correct number of
> >spaces I need. Each record/row must be a certain width (224 characters).
> >When I put in a length of 28 for one of my blank fields, it only puts 11
> >spaces in that field -- I've tried increasing the length, but still only get
> >11 spaces in the .txt file. What am I doing wrong? Thanks in advance!
>
> A Table field trims trailing blanks. Try exporting from a Query rather than
> from the table. In 2003 and earlier you can create an Export Specification, I
> don't have 2007 installed so I'm not sure how or whether to do so there; but
> you can put in a calculated field
>
> ExpField1: [Field1] & String(" ", 27 - Len([Field1]))
>
> to tack the needed number of blanks on to the field value.
> --
>
> John W. Vinson [MVP]
>

On Mon, 6 Apr 2009 06:36:17 -0700, Jen Scott
<(E-Mail Removed)> wrote:

>When I put this into my query it runs, but returns "Error#" in the field
>name. I think this might work when there are values in the fields, but my
>field is blank and I need to export a certain # of blank spaces (16 to be
>exact) before the first value in the fixed width text file. If I try to
>export it with the function, it gives me another error and won't export.

If the field is ALWAYS blank don't export it at all - just use a calculated
field ExpBlank: String(16, " ")

You can use the NZ() function to replace a string with a null value, or an
expression like IIF(IsNull([fieldname]), String(16, " "), <expression>)
--

John W. Vinson [MVP]