Printing Envelops using report

[Frank]

I have a report that I use to print envelopes. It is based on a query that
has fields: name, addresses 1, address 2, city, state. and zip code. On the
report I have four rows that show all the data in above mentioned fields.

In case there is no data in the address 2 field, I want the report to
reflect only three rows instead of four thus having the fourth row move up
in the report so there won't be a double space between the addresses rows
and the city, state, and zip code row.

What is the best way to accomplish this?

Any assistance is greatly appreciated.

 

[fredg]

I have a report that I use to print envelopes. It is based on a query that
has fields: name, addresses 1, address 2, city, state. and zip code. On the
report I have four rows that show all the data in above mentioned fields.

In case there is no data in the address 2 field, I want the report to
reflect only three rows instead of four thus having the fourth row move up
in the report so there won't be a double space between the addresses rows
and the city, state, and zip code row.

What is the best way to accomplish this?

Any assistance is greatly appreciated.

Easiest way:
Set [Address2] control's Can Shrink property to Yes.

Alternatively, you can use an expression in an unbound control to show
both addresses or just the one if the other is null:
=IIf(IsNull([Address2]),[Address1],[Address1] & chr(13) & chr(10) &
[Address2])
Make sure the name of this control is not "Address1" or "Address2".

 

[Frank]

Fred:

Thanks a bunch for quick reply. Will try your suggestions and report back
results.

Frank

I have a report that I use to print envelopes. It is based on a query
that
has fields: name, addresses 1, address 2, city, state. and zip code. On
the
report I have four rows that show all the data in above mentioned fields.

In case there is no data in the address 2 field, I want the report to
reflect only three rows instead of four thus having the fourth row move
up
in the report so there won't be a double space between the addresses rows
and the city, state, and zip code row.

What is the best way to accomplish this?

Any assistance is greatly appreciated.

Easiest way:
Set [Address2] control's Can Shrink property to Yes.

Alternatively, you can use an expression in an unbound control to show
both addresses or just the one if the other is null:
=IIf(IsNull([Address2]),[Address1],[Address1] & chr(13) & chr(10) &
[Address2])
Make sure the name of this control is not "Address1" or "Address2".

 

[Frank]

Fred:

Tried your expression - It works great except that the fourth row when two
addresses are present does not move up when address2 is not present. In
short, if there are two addresses present, the two addresses lines are
spaced too closely together thus making the address lines unballanced.

Is it possible to put all the fields as noted below into one expression?
Maybe this will work? If so, what would the expression look like?

Also, what are the chr(10) and chr(13) parts of the expression? I know it
has something to do with moving data to the next line.

My layout is as follows:

=trim([salutation] &" " &[firstname]&" "&[spouse]& " "[lastname])

=iif(isnull([address2]),[address1],[address1] &chr(13) &chr(10) &[address2])

=trim([city] &"," &[state]& "," &[zip])



Thanks once again for any further comments you may have.

Frank

Fred:

Thanks a bunch for quick reply. Will try your suggestions and report back
results.

Frank

I have a report that I use to print envelopes. It is based on a query
that
has fields: name, addresses 1, address 2, city, state. and zip code. On
the
report I have four rows that show all the data in above mentioned
fields.

In case there is no data in the address 2 field, I want the report to
reflect only three rows instead of four thus having the fourth row move
up
in the report so there won't be a double space between the addresses
rows
and the city, state, and zip code row.

What is the best way to accomplish this?

Any assistance is greatly appreciated.

Easiest way:
Set [Address2] control's Can Shrink property to Yes.

Alternatively, you can use an expression in an unbound control to show
both addresses or just the one if the other is null:
=IIf(IsNull([Address2]),[Address1],[Address1] & chr(13) & chr(10) &
[Address2])
Make sure the name of this control is not "Address1" or "Address2".

 

[Frank]

Fred:

Tried your expression - It works great except that the fourth row when two
addresses are present does not move up when address2 is not present. In
short, if there are two addresses present, the two addresses lines are
spaced too closely together thus making the address lines unballanced.

Is it possible to put all the fields as noted below into one expression?
Maybe this will work? If so, what would the expression look like?

Also, what are the chr(10) and chr(13) parts of the expression? I know it
has something to do with moving data to the next line.

My layout is as follows:

=trim([salutation] &" " &[firstname]&" "&[spouse]& " "[lastname])

=iif(isnull([address2]),[address1],[address1] &chr(13) &chr(10) &[address2])

=trim([city] &"," &[state]& "," &[zip])



Thanks once again for any further comments you may have.

Frank

 

[fredg]

Fred:

Tried your expression - It works great except that the fourth row when two
addresses are present does not move up when address2 is not present. In
short, if there are two addresses present, the two addresses lines are
spaced too closely together thus making the address lines unballanced.

Is it possible to put all the fields as noted below into one expression?
Maybe this will work? If so, what would the expression look like?

Also, what are the chr(10) and chr(13) parts of the expression? I know it
has something to do with moving data to the next line.

My layout is as follows:

=trim([salutation] &" " &[firstname]&" "&[spouse]& " "[lastname])

=iif(isnull([address2]),[address1],[address1] &chr(13) &chr(10) &[address2])

=trim([city] &"," &[state]& "," &[zip])

Thanks once again for any further comments you may have.

Frank

I have a report that I use to print envelopes. It is based on a query that
has fields: name, addresses 1, address 2, city, state. and zip code. On
the report I have four rows that show all the data in above mentioned
fields.

In case there is no data in the address 2 field, I want the report to
reflect only three rows instead of four thus having the fourth row move
up in the report so there won't be a double space between the addresses
rows and the city, state, and zip code row.

What is the best way to accomplish this?

Any assistance is greatly appreciated.

Is that Address control the same height as the other 2 controls?
Or is it taller?

Set the height of each control to 0.188"
position the Address and City/State controls so that the top of the
control is just under the bottom of the control above it
Make that clearance the same for all controls.
Set the Address control's CanGrow property to Yes.

The City/State should show just under the Address line.
You can now change the Height of the 3 control's (Taller or shorter)
to whatever best fits the Font Size you are using.

If you are printing directly on an envelope, that's all you need do.

If you are printing all addresses together on a sheet of paper, also
set the Detail Section CanGrow to Yes (optional).

The Chr(13) & Chr(10) are the Access codes for carriage return and
line feed. In Access, you must use both, in that order, to space to
the beginning of the next line.

 

[fredg]

Fred:

Tried your expression - It works great except that the fourth row when two
addresses are present does not move up when address2 is not present. In
short, if there are two addresses present, the two addresses lines are
spaced too closely together thus making the address lines unballanced.

Is it possible to put all the fields as noted below into one expression?
Maybe this will work? If so, what would the expression look like?

Also, what are the chr(10) and chr(13) parts of the expression? I know it
has something to do with moving data to the next line.

My layout is as follows:

=trim([salutation] &" " &[firstname]&" "&[spouse]& " "[lastname])

=iif(isnull([address2]),[address1],[address1] &chr(13) &chr(10) &[address2])

=trim([city] &"," &[state]& "," &[zip])

Thanks once again for any further comments you may have.

Frank

I have a report that I use to print envelopes. It is based on a query that
has fields: name, addresses 1, address 2, city, state. and zip code. On
the report I have four rows that show all the data in above mentioned
fields.

In case there is no data in the address 2 field, I want the report to
reflect only three rows instead of four thus having the fourth row move
up in the report so there won't be a double space between the addresses
rows and the city, state, and zip code row.

What is the best way to accomplish this?

Any assistance is greatly appreciated.

Is that Address control the same height as the other 2 controls?
Or is it taller?

Set the height of each control to 0.188"
position the Address and City/State controls so that the top of the
control is just under the bottom of the control above it
Make that clearance the same for all controls.
Set the Address control's CanGrow property to Yes.

The City/State should show just under the Address line.
You can now change the Height of the 3 control's (Taller or shorter)
to whatever best fits the Font Size you are using.

If you are printing directly on an envelope, that's all you need do.

If you are printing all addresses together on a sheet of paper, also
set the Detail Section CanGrow to Yes (optional).

The Chr(13) & Chr(10) are the Access codes for carriage return and
line feed. In Access, you must use both, in that order, to space to
the beginning of the next line.

I may have answered a question you did not ask.
Upon re-reading you reply to my post, I'm still not sure what you are
asking.

So, in addition to this previous reply .....

When concatenating text with multiple lines in a control, the line
spacing between these lines is a bit less than the spacing between
text placed in 2 or more controls.

If you wish all of the text to have equal spacing, then instead of
using 3 controls in the address, use 1 unbound control.

Set it's Control source to:
=Trim([First Name]) & " " & [LastName] & Chr(13) & Chr(10) &
IIf(IsNull([Address2]),[Address1] & Chr(13) & Chr(10),[Address1] &
Chr(13) & Chr(10) & [Address2] & Chr(13) & Chr(10)) & [City] & ", " &
[State] & " " & [Zip]

Size this control to the height of just 1 line.

Set this control's CanGrow property to Yes.

Note... the above control source expression can be written in l
different ways. This is most likely the easiest for you to follow.

 

[Frank]

Fred:

Thanks lots for follow up. I did exactly as you suggested and everything
works perfectly now. Thanks once again for you most timely and professional
assistance.

Frank