Fill Remaining Text Field With Blanks

[Gary Wilson]

I am trying to create a report that will be exported to a fixed width text
file. With that said, I have a field called "firstname" that has a field
length of 15. If I have a value of "John", then only uses 4 characters. Due
to the report being fixed width, I need it to use all 15 characters (in this
case "John" followed by 11 spaces).

How can I accomplish this?

 

[Allen Browne]

You could use a text box with Control Source like this:
=[FirstName] & Space(15 - Nz(Len([FirstName],0))

Traps:
1. Change the Name property of this text box as well as its Control Source.
Access gets confused if the control has the same name as a field but is
bound to something else.

2. A proportional font won't line up for you based on number of characters.
You may need to use a fixed-width font such as Courier New.

3. It might be easier just to place multiple text boxe beside each other
rather than line them up like this.

 

[Gary Wilson]

I appreciate your help with this! It worked perfectly. I just have one more
question. If I wanted to have leading spaces, would I just reverse the
formula? For example: =Space(15 -Nz(Len([FirstName],0)) & [FirstName]

You could use a text box with Control Source like this:
=[FirstName] & Space(15 - Nz(Len([FirstName],0))

Traps:
1. Change the Name property of this text box as well as its Control Source.
Access gets confused if the control has the same name as a field but is
bound to something else.

2. A proportional font won't line up for you based on number of characters.
You may need to use a fixed-width font such as Courier New.

3. It might be easier just to place multiple text boxe beside each other
rather than line them up like this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a report that will be exported to a fixed width text
file. With that said, I have a field called "firstname" that has a field
length of 15. If I have a value of "John", then only uses 4 characters.
Due
to the report being fixed width, I need it to use all 15 characters (in
this
case "John" followed by 11 spaces).

How can I accomplish this?

 

[Gary Wilson]

I just answered my own question. I tried reversing the control source code
and it worked. It gave me leading spaces followed by text.

Thanks again for your help!

You could use a text box with Control Source like this:
=[FirstName] & Space(15 - Nz(Len([FirstName],0))

Traps:
1. Change the Name property of this text box as well as its Control Source.
Access gets confused if the control has the same name as a field but is
bound to something else.

2. A proportional font won't line up for you based on number of characters.
You may need to use a fixed-width font such as Courier New.

3. It might be easier just to place multiple text boxe beside each other
rather than line them up like this.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create a report that will be exported to a fixed width text
file. With that said, I have a field called "firstname" that has a field
length of 15. If I have a value of "John", then only uses 4 characters.
Due
to the report being fixed width, I need it to use all 15 characters (in
this
case "John" followed by 11 spaces).

How can I accomplish this?