Concatination (I think?)

  • Thread starter Thread starter Sue
  • Start date Start date
S

Sue

I have some address fields in a table that I need to use
to create an invoice from a report. Name, Address1,
Address2 Town, County, Postcode.

The problem is that the addresses are not all a
consistant length, some addresses don't have a second
line for the address. Therefore when I align them
vertically on the report I get gaps. Is there anyway I
can get around this?

They need to be

Name
Address1
Address2
Town
County
Postcode

ANy help greatfully received.
 
I have some address fields in a table that I need to use
to create an invoice from a report. Name, Address1,
Address2 Town, County, Postcode.

The problem is that the addresses are not all a
consistant length, some addresses don't have a second
line for the address. Therefore when I align them
vertically on the report I get gaps. Is there anyway I
can get around this?

They need to be

Name
Address1
Address2
Town
County
Postcode

ANy help greatfully received.
Click to expand...

Set each control's CanShrink property to Yes.
There can be no other control on the same line.
I don't see what the field's length has to do with your problem.
Just make the field wide enough to display the longest text.
If you don't mind long text spilling over to a second line, make the
control's CanGrow property Yes also.
 
If they are in different textboxes and you don't mind them shrinking, you
can set the CanShrink property of the textbox to Yes. This will allow the
empty textboxes to shrink down and get rid of the space. This will work as
long as there is nothing else in-line horizontally on the report with the
textbox. It will also cause everything below in the same section to move up
and the other sections to move up also, if you set the CanShrink of the
section to Yes.

Another option is to place all of the address into a single textbox and
concatenate it. To do that, you will use a calculated textbox. Set its
control source to

=[Name] & vbCrLf & [Address1] & vbCrLf & [Address2] + vbCrlf & [Town] & ", "
& [County] & " " & [Postcode]

The + instead of the & takes advantage of the fact that "Null will propogate
and equation". This way, if Address 2 is Null then Null + vbCrlf is Null,
effectively eliminating the carriage return following Address2 so that you
don't get a blank line.

ps. Name is not a good name for a field, it is a reseved word.
 
Sure.

Just set the Can Shrink property of each field to Yes. If
there is no data in the field, it won't print that line.
Be sure the controls don't overlap, which could cause the
Can Shrink (and the Can Grow) properties to malfunction.

HTH
Kevin Sprinkel
 
Correction, the vbCrLf constant won't work in the textbox. Use Chr(13) &
Chr(10) instead for each instance. If + was used before the vbCrLf, then use
+ between these also.

--
Wayne Morgan
MS Access MVP


Wayne Morgan said:
If they are in different textboxes and you don't mind them shrinking, you
can set the CanShrink property of the textbox to Yes. This will allow the
empty textboxes to shrink down and get rid of the space. This will work as
long as there is nothing else in-line horizontally on the report with the
textbox. It will also cause everything below in the same section to move up
and the other sections to move up also, if you set the CanShrink of the
section to Yes.

Another option is to place all of the address into a single textbox and
concatenate it. To do that, you will use a calculated textbox. Set its
control source to

=[Name] & vbCrLf & [Address1] & vbCrLf & [Address2] + vbCrlf & [Town] & ", "
& [County] & " " & [Postcode]

The + instead of the & takes advantage of the fact that "Null will propogate
and equation". This way, if Address 2 is Null then Null + vbCrlf is Null,
effectively eliminating the carriage return following Address2 so that you
don't get a blank line.

ps. Name is not a good name for a field, it is a reseved word.

--
Wayne Morgan
MS Access MVP


Sue said:
I have some address fields in a table that I need to use
to create an invoice from a report. Name, Address1,
Address2 Town, County, Postcode.

The problem is that the addresses are not all a
consistant length, some addresses don't have a second
line for the address. Therefore when I align them
vertically on the report I get gaps. Is there anyway I
can get around this?

They need to be

Name
Address1
Address2
Town
County
Postcode

ANy help greatfully received.
Click to expand...
Click to expand...
 
Back
Top