Adding Spaces instead of zeros

[Linda]

I need to keep 30 spaces in a field even though the data that I have in
each record may be less than 30. How do I add blank spaces to a field?


Thanks Linda

 

[Duane Hookom]

Can you provide us with the reason that you need to save the spaces? You can
use an update query to add spaces to the right in a field.

 

[Linda]

Hi Duane,
This is what the field could look like:
TransNo
2347r8
45re45
23no0
qw4

I'm creating a file to upload to a mainframe. All the fields are fixed width
and, in some cases, the data going into the fields will not be the same.

In the past I've used this in a query:
TextVI: [Vend-Inv-No] & Left([Spaces],[DifVI])
after I've created a separate "Spaces" field for each record . The default
value for the Spaces field was " ". I would
subtract the length of each Vend-Inv-No from the Spaces field and call it
DifVI then that would be concatenated on to the Vend-Inv-No. It worked.
But.......

Is there an easier way to make sure that each variable length record will be
30 characters regardless of the length of each piece of data? If each record
were the same, I know I could just concatenate the remaining characters, but
the records could be of variable length.

I hope this explains.

 

[Duane Hookom]

Have you tried the export wizard? You can set up a fixed width export and
save the export definition. You can also create a query for export. The
field can be concatenated together like:
ExpField: Left(YourField & Space(40),40) & Left(NextField & Space(12), 12)

--
Duane Hookom
MS Access MVP

Hi Duane,
This is what the field could look like:
TransNo
2347r8
45re45
23no0
qw4

I'm creating a file to upload to a mainframe. All the fields are fixed width
and, in some cases, the data going into the fields will not be the same.

In the past I've used this in a query:
TextVI: [Vend-Inv-No] & Left([Spaces],[DifVI])
after I've created a separate "Spaces" field for each record . The default
value for the Spaces field was " ". I would
subtract the length of each Vend-Inv-No from the Spaces field and call it
DifVI then that would be concatenated on to the Vend-Inv-No. It worked.
But.......

Is there an easier way to make sure that each variable length record will be
30 characters regardless of the length of each piece of data? If each record
were the same, I know I could just concatenate the remaining characters, but
the records could be of variable length.

I hope this explains.

 

[Linda]

Okay, I get it. I can do this Left([TransNo] & Space$(20),11). This way I'm
concatenating 20 spaces(right?) but then I'm counting 11 characters from the
left. How cool.
Yes that will work. I could have saved myself a lot of trouble if I'd asked
you about this on the last project.

Thanks, Linda

Have you tried the export wizard? You can set up a fixed width export and
save the export definition. You can also create a query for export. The
field can be concatenated together like:
ExpField: Left(YourField & Space(40),40) & Left(NextField & Space(12), 12)

--
Duane Hookom
MS Access MVP

Hi Duane,
This is what the field could look like:
TransNo
2347r8
45re45
23no0
qw4

I'm creating a file to upload to a mainframe. All the fields are fixed width
and, in some cases, the data going into the fields will not be the same.

In the past I've used this in a query:
TextVI: [Vend-Inv-No] & Left([Spaces],[DifVI])
after I've created a separate "Spaces" field for each record . The default
value for the Spaces field was " ". I would
subtract the length of each Vend-Inv-No from the Spaces field and call it
DifVI then that would be concatenated on to the Vend-Inv-No. It worked.
But.......

Is there an easier way to make sure that each variable length record

will

be
30 characters regardless of the length of each piece of data? If each record
were the same, I know I could just concatenate the remaining characters, but
the records could be of variable length.

I hope this explains.




You
can

have

in