fixed field - how to keep fields in-tact?

[Guest]

Using Access2000, I am concatenating a number of fields in order to compress
the printing of multiple lines to use as little vertical space as possible.
(can grow) At issue is that Access automatically trims the text fields so
line by line, fields don't up. I want to retain the field length when I
concatenate these variables. It would solve all the formatting issues that
are occurring. Is there a trick that i am missing?

Thanks for any advice you can offer.

 

[Allen Browne]

You can programmatically add a space between the fields.

There are 2 concatenation operators, + and &, and they handle Nulls
differently:
Null + " " = Null
Null & " " = " "

Therefore you can use:
=[FirstName] + " " & [Surname]
and it trims the space it the FirstName is Null.

 

[Guest]

thanks for your response, however the problem persists.

Example - concatenate 2 fixed length fields A1 & " " & A2 where
A1 is 20 in width and value of Hello and
A2 is 10 and value World - it will look like this: Hello World.
I want to retain the field widths so it looks like Hello
World .

I used the Space function to add spaces at the end of A1 to equal the
difference between the length of the value inside A1 and 20.
I set the Can Shrink to No to make sure the text box is not trying to
collapse when I run the report.
The intermediate window, when I run the code it looks perfect - all the
spacing is correct.
The report looks bad because the spacing that I added in programming doesn't
hold up.
I am stumped...

You can programmatically add a space between the fields.

There are 2 concatenation operators, + and &, and they handle Nulls
differently:
Null + " " = Null
Null & " " = " "

Therefore you can use:
=[FirstName] + " " & [Surname]
and it trims the space it the FirstName is Null.

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

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

Using Access2000, I am concatenating a number of fields in order to
compress
the printing of multiple lines to use as little vertical space as
possible.
(can grow) At issue is that Access automatically trims the text fields so
line by line, fields don't up. I want to retain the field length when I
concatenate these variables. It would solve all the formatting issues
that
are occurring. Is there a trick that i am missing?

Thanks for any advice you can offer.

 

[Allen Browne]

Anna, I follow you with the logic of combining several fields into one, so
it prints as one continuous sentence instead of the space of each individual
text box.

I don't understand the bit about A1 having a width of 20 and A2 a width of
10. That seems unnecessary if you are combining them into one text box. If
you set the text box's Can Grow property to Yes, it will grow to the desired
height vertically. But CanGrow/CanShrink have no effect on the horizontal
width of the text box.

If you really are trying to calculate how wide you need the text box to
handle the concatenated text, this page might help:
http://www.lebans.com/textwidth-height.htm

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

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

thanks for your response, however the problem persists.

Example - concatenate 2 fixed length fields A1 & " " & A2 where
A1 is 20 in width and value of Hello and
A2 is 10 and value World - it will look like this: Hello World.
I want to retain the field widths so it looks like Hello
World .

I used the Space function to add spaces at the end of A1 to equal the
difference between the length of the value inside A1 and 20.
I set the Can Shrink to No to make sure the text box is not trying to
collapse when I run the report.
The intermediate window, when I run the code it looks perfect - all the
spacing is correct.
The report looks bad because the spacing that I added in programming
doesn't
hold up.
I am stumped...

You can programmatically add a space between the fields.

There are 2 concatenation operators, + and &, and they handle Nulls
differently:
Null + " " = Null
Null & " " = " "

Therefore you can use:
=[FirstName] + " " & [Surname]
and it trims the space it the FirstName is Null.

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

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

Using Access2000, I am concatenating a number of fields in order to
compress
the printing of multiple lines to use as little vertical space as
possible.
(can grow) At issue is that Access automatically trims the text fields
so
line by line, fields don't up. I want to retain the field length when
I
concatenate these variables. It would solve all the formatting issues
that
are occurring. Is there a trick that i am missing?

Thanks for any advice you can offer.

 

[Guest]

Allen -
It is necessary that I retain not the variable width but the field width of
each variable that I add to the concat string. I must retain the spaces that
fill the width of the field - and that is really the point of all of this.
It may not be intuitive I know but Access has this nifty way of trimming
everything and that is the cause of the problem for me. btw... I know there
is something happening in the report itself because the immediate window
shows all the correct spacing within my string.

Any more ideas? Total frustration has set in!
Anna

Anna, I follow you with the logic of combining several fields into one, so
it prints as one continuous sentence instead of the space of each individual
text box.

I don't understand the bit about A1 having a width of 20 and A2 a width of
10. That seems unnecessary if you are combining them into one text box. If
you set the text box's Can Grow property to Yes, it will grow to the desired
height vertically. But CanGrow/CanShrink have no effect on the horizontal
width of the text box.

If you really are trying to calculate how wide you need the text box to
handle the concatenated text, this page might help:
http://www.lebans.com/textwidth-height.htm

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

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

thanks for your response, however the problem persists.

Example - concatenate 2 fixed length fields A1 & " " & A2 where
A1 is 20 in width and value of Hello and
A2 is 10 and value World - it will look like this: Hello World.
I want to retain the field widths so it looks like Hello
World .

I used the Space function to add spaces at the end of A1 to equal the
difference between the length of the value inside A1 and 20.
I set the Can Shrink to No to make sure the text box is not trying to
collapse when I run the report.
The intermediate window, when I run the code it looks perfect - all the
spacing is correct.
The report looks bad because the spacing that I added in programming
doesn't
hold up.
I am stumped...

You can programmatically add a space between the fields.

There are 2 concatenation operators, + and &, and they handle Nulls
differently:
Null + " " = Null
Null & " " = " "

Therefore you can use:
=[FirstName] + " " & [Surname]
and it trims the space it the FirstName is Null.

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

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

Using Access2000, I am concatenating a number of fields in order to
compress
the printing of multiple lines to use as little vertical space as
possible.
(can grow) At issue is that Access automatically trims the text fields
so
line by line, fields don't up. I want to retain the field length when
I
concatenate these variables. It would solve all the formatting issues
that
are occurring. Is there a trick that i am missing?

Thanks for any advice you can offer.

 

[Allen Browne]

Okay, if you want a fixed width for each field, the simplest solution would
be to leave each text box in place, side-by-side, for each field instead of
concatenating them?

If that can't work, you might consider using a fixed-width font (such as
Courier New), and then padding the text with spaces to make it align. Use
Len() to get the length of the existing field, and Space() to add the extra
spaces. This kind of thing:
=[FirstName] & Space(20 - Nz(Len([FirstName]),0)) & [Surname]

If you want to do that with a proportional-width font, it will take more
effort, but the link I gave you to Stephen Leban's page will let you
calculate the width of the text.

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

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

Allen -
It is necessary that I retain not the variable width but the field width
of
each variable that I add to the concat string. I must retain the spaces
that
fill the width of the field - and that is really the point of all of this.
It may not be intuitive I know but Access has this nifty way of trimming
everything and that is the cause of the problem for me. btw... I know
there
is something happening in the report itself because the immediate window
shows all the correct spacing within my string.

Any more ideas? Total frustration has set in!
Anna

Anna, I follow you with the logic of combining several fields into one,
so
it prints as one continuous sentence instead of the space of each
individual
text box.

I don't understand the bit about A1 having a width of 20 and A2 a width
of
10. That seems unnecessary if you are combining them into one text box.
If
you set the text box's Can Grow property to Yes, it will grow to the
desired
height vertically. But CanGrow/CanShrink have no effect on the horizontal
width of the text box.

If you really are trying to calculate how wide you need the text box to
handle the concatenated text, this page might help:
http://www.lebans.com/textwidth-height.htm

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

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

thanks for your response, however the problem persists.

Example - concatenate 2 fixed length fields A1 & " " & A2 where
A1 is 20 in width and value of Hello and
A2 is 10 and value World - it will look like this: Hello World.
I want to retain the field widths so it looks like Hello
World .

I used the Space function to add spaces at the end of A1 to equal the
difference between the length of the value inside A1 and 20.
I set the Can Shrink to No to make sure the text box is not trying to
collapse when I run the report.
The intermediate window, when I run the code it looks perfect - all the
spacing is correct.
The report looks bad because the spacing that I added in programming
doesn't
hold up.
I am stumped...



:

You can programmatically add a space between the fields.

There are 2 concatenation operators, + and &, and they handle Nulls
differently:
Null + " " = Null
Null & " " = " "

Therefore you can use:
=[FirstName] + " " & [Surname]
and it trims the space it the FirstName is Null.

Using Access2000, I am concatenating a number of fields in order to
compress
the printing of multiple lines to use as little vertical space as
possible.
(can grow) At issue is that Access automatically trims the text
fields
so
line by line, fields don't up. I want to retain the field length
when
I
concatenate these variables. It would solve all the formatting
issues
that
are occurring. Is there a trick that i am missing?

Thanks for any advice you can offer.

 

[Chuck]

Allen -
It is necessary that I retain not the variable width but the field width of
each variable that I add to the concat string. I must retain the spaces that
fill the width of the field - and that is really the point of all of this.
It may not be intuitive I know but Access has this nifty way of trimming
everything and that is the cause of the problem for me. btw... I know there
is something happening in the report itself because the immediate window
shows all the correct spacing within my string.

Any more ideas? Total frustration has set in!
Anna

Anna, I follow you with the logic of combining several fields into one, so
it prints as one continuous sentence instead of the space of each individual
text box.

I don't understand the bit about A1 having a width of 20 and A2 a width of
10. That seems unnecessary if you are combining them into one text box. If
you set the text box's Can Grow property to Yes, it will grow to the desired
height vertically. But CanGrow/CanShrink have no effect on the horizontal
width of the text box.

If you really are trying to calculate how wide you need the text box to
handle the concatenated text, this page might help:
http://www.lebans.com/textwidth-height.htm

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

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

thanks for your response, however the problem persists.

Example - concatenate 2 fixed length fields A1 & " " & A2 where
A1 is 20 in width and value of Hello and
A2 is 10 and value World - it will look like this: Hello World.
I want to retain the field widths so it looks like Hello
World .

I used the Space function to add spaces at the end of A1 to equal the
difference between the length of the value inside A1 and 20.
I set the Can Shrink to No to make sure the text box is not trying to
collapse when I run the report.
The intermediate window, when I run the code it looks perfect - all the
spacing is correct.
The report looks bad because the spacing that I added in programming
doesn't
hold up.
I am stumped...



:

You can programmatically add a space between the fields.

There are 2 concatenation operators, + and &, and they handle Nulls
differently:
Null + " " = Null
Null & " " = " "

Therefore you can use:
=[FirstName] + " " & [Surname]
and it trims the space it the FirstName is Null.

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

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

Using Access2000, I am concatenating a number of fields in order to
compress
the printing of multiple lines to use as little vertical space as
possible.
(can grow) At issue is that Access automatically trims the text fields
so
line by line, fields don't up. I want to retain the field length when
I
concatenate these variables. It would solve all the formatting issues
that
are occurring. Is there a trick that i am missing?

Thanks for any advice you can offer.

You may already have it without realizing it.

Open the report in design view. Click on the 'Field List' icon to display all
the fields that are available for that report. Do not use a text box to
concatenate the fields. Instead,drag each field, one by one, from the list
into the design section. Delete the field label box that automatically comes
along with the field box. Adjust the width of the field box for each field to
be equal the lenght of the field as shown in the table design view. Have each
successive field positioned so that its left edge just touches the right edge
of the previous field. Set the text align property to 'left' for all fields.
Don't make the total line length greater than your paper width minus the widths
of the left and right margins. You may have to set the page layout to
portrait.

Just a wizard prodder
Chuck
....