ENVELOPE PRINTING: How to combine city/state/zip code fields into a single field (no extra spaces)?

[StGermain]

Thanks for help with this totally-new Access user question.

I am trying to create a report to print mailing envelopes.

I tried to research how to do this online, but despite several hours of waste time it still does not work.

Please see the two screenshots.

Here is what I put in the box for the last line:

=[City] & (", "+[State/Province]) & (" "+[Zip/Postal Code])


You can see the screenshot with the error

Anybody's help is appreciated.
Thanks

[Welshgasman]

I never use + for concatenation, despite it working in some ways.
Just use the & instead. Hopefuly you ALWAYS have values for these, else you will need to test for what you have?

=[City] & ", " & [State/Province] & "  " & [Zip/Postal Code]

[StGermain]

Thanks for your reply.

I pasted that in, but still get errors as shown in screen shot

[Micron]

In a report you can refer to fields in the recordsource even if there are no controls bound to those fields. Not sure you can do that on a form - never tried. Regardless, there is no evidence in your post that you have those fields in the recordsource or as controls on the form. If not, you can't use them in your concatenation.

EDIT - Just tested and yes, you can refer to a recordsource field for a form if there is no control on the form that is bound to it. Anyway, that's the question before you. Are your 3 fields part of the recordsource at least? Plus, you have a 2 part problem - circular reference in that expression. Likely one of the elements in the reference is also referencing the same thing. Click on the error and see what info it gives you for the circular reference.

[StGermain]

Here are the fields. I think they do match the report in question?

[Welshgasman]

I am also puzzled by that circular reference?
What have you called the textbox that holds the concatenation data?

[StGermain]

Yes, you nailed it! I had named the textbox "city" (i.e. same as already-existing "city" field).

I changed it to "Last Address Line" -- and now it displays correctly.

Thanks so much.

ANOTHER SMALL PROBLEM maybe you can solve for me too?

The print view shows a SECOND "blank" page -- with a gray bar at the top.

Here is what it looks like . . .

And also here is what the design view looks like . . .

[pbaldy]

I never use + for concatenation, despite it working in some ways.
Just use the & instead. Hopefuly you ALWAYS have values for these, else you will need to test for what you have?

=[City] & ", " & [State/Province] & "  " & [Zip/Postal Code]

Using the + as was done is a neat trick to account for Null values. + will propagate Null, & will not. Thus if [State/Province] is Null below, you will not see the comma. You'll only get the comma if there's a value in the field:

=[City] & (", "+[State/Province]) & (" "+[Zip/Postal Code])

Note only 1 comma:

?"blah" & ("," + null) & ("," + "test")
blah,test

[Welshgasman]

Yes, you nailed it! I had named the textbox "city" (i.e. same as already-existing "city" field).

I changed it to "Last Address Line" -- and now it displays correctly.

Thanks so much.

ANOTHER SMALL PROBLEM maybe you can solve for me too?

The print view shows a SECOND "blank" page -- with a gray bar at the top.

Here is what it looks like . . .

And also here is what the design view looks like . . .

Have you adjusted the report for the envelope depth?
Have you selected the correct size option in design mode?