Report contains address with blank lines (1 Viewer)

[Mike Hughes]

I'm sure this might have been answered before but I can't find the answer.

I have a report with the following fields

Name
Address 1
Address 2
Address 3
City, State ZIP

When there is not address 3 and the report prints it is leaving a space between address 2 and City, State ZIP

Is there a way to remove this space when there is no Line 3 or even when there is no Line 2?

 

[DCrake]

Have you set the shrink and grow properties correctly?

 

[Mike Hughes]

I'm sorry but I'm not sure where this is done.
I have attached a picture of the document

 

[vbaInet]

Put this in just one textbox:

=[Address 1] + Chr(13) & Chr(10) + [Address 2] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [City] & ", " & [State] & " " & [Zip]

Set the Can Grow and Can Shrink properties of the textbox to YES. You will find them under the Format tab of the textbox's Property Sheet.

 

[Mike Hughes]

OK I set textbox can grow and can shrink properties of these text boxes to yes.

A1,A2,A3,city,state zip

Set the Can Grow and Can Shrink properties of the textbox to YES. You will find them under the Format tab of the textbox's Property Sheet.

I still don't understand where you want me to put this:

=[Address 1] + Chr(13) & Chr(10) + [Address 2] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [City] & ", " & [State] & " " & [Zip]

I'm lost sorry.

 

[vbaInet]

Mike, I'm sure it was explained clearly enough:

OK I set textbox can grow and can shrink properties of these text boxes to yes.

A1,A2,A3,city,state zip

Set the Can Grow and Can Shrink properties of THE textbox to YES. You will find them under the Format tab of the textbox's Property Sheet.

The textbox mentioned in my first statement. I mentioned one textbox not all textboxes.

I still don't understand where you want me to put this:

=[Address 1] + Chr(13) & Chr(10) + [Address 2] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [City] & ", " & [State] & " " & [Zip]

Put this in just ONE textbox:

In the Control Source property.

I have put one too many [Address 3], so use this:

=[Address 1] + Chr(13) & Chr(10) + [Address 2] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10) + [City] & ", " & [State] & " " & [Zip]

 

[boblarson]

The Concatenation method vbaInet shows is the method I normally use too. But with the extra text boxes you have, you should be able to set the Can Shrink property of the text boxes AND the DETAIL SECTION and they should not display for you if there is no data and they should shrink up EXCEPT they won't because you have other controls on the same plane as the address ones (the ones for CaseID, SSN, DOB). Because those occupy the same horizontal space, there is no way for the report to shrink that up. Hence the need to use the concatenation method.

 

[Mike Hughes]

vbaInet, I'm not trying to piss you off, I'm just stupid.

See attachment
I have changed the NCPADDRESSLINE4 shrink and grow properties to yes
I just don't understand

When you say "Put this in just ONE textbox" are you saying to put =[Address 1] + Chr(13) & Chr(10) + [Address 2] + Chr(13) & Chr(10) + [Address 3] + Chr(13) & Chr(10)
into the NCPADDRESSLINE4 text box?
When I do this and try to open the report I'm asked to enter A1,A2,A3, city, state zip and this doesn't seem right.

 

[boblarson]

What you should do is to take the code that vbaInet gave you and substitute where he has Address1 and use your field - NCPADDLINE1 and where he used Address2 in that same formula you substitute NCPADDLINE2, etc.

And then you put that formula in the NCPADDLINE1 Control Source and get RID OF the three other text boxes.

 

[Mike Hughes]

I would like to attach a stripped version of the DB.
After opening the DB go to FORMS and open PRINTING and click on the printer icon.

Would someone pleae review and show me in this DB what needs to be done to fix it.

Thanks for any help you can give. Mike

 

[vbaInet]

I was simply reiterating.

It's pretty easy and it's something you can do. I will attempt to make it clearer:

=[[COLOR=Red]Address 1[/COLOR]] + Chr(13) & Chr(10) + [[COLOR=Red]Address 2[/COLOR]] + Chr(13) & Chr(10) + [[COLOR=Red]Address 3[/COLOR]] + Chr(13) & Chr(10) + [[COLOR=Red]City[/COLOR]] & ", " & [[COLOR=Red]State[/COLOR]] & " " & [[COLOR=Red]Zip[/COLOR]]

All the bits in red are supposed to represent field names. In your first post you mentioned exactly those as your field names so I don't see why it's such a confusing thing to simply replace those with their corresponding field names. Don't change anything else, only the red bits.

A textbox has a Control Source property, once you've type in the right field names, copy and paste it into the Control Source property of the textbox. Set the Can Grow and Can Shrink property. You only need to copy and paste it into one textbox's Control Source property. Done!

 

[boblarson]

Hey vbaInet - I tried your formula on the report but it leaves a carriage return and line feed (looks like a square) character visible on the form in the blank Address2 spot.

Also, you don't want City State and Zip in there as he has it in the NCPADDLINE4 field.

I was trying to play with brackets and such like shown here in order to avoid the extra CRLF when there is nothing in the field.

 

[vbaInet]

Hey Bob, since you've amended the field names can you copy and paste it here for me ... and I will have a look.

 

[boblarson]

Hey Bob, since you've amended the field names can you copy and paste it here for me ... and I will have a look.

=[NCPADDLINE1]+Chr(13) & Chr(10)+[NCPADDLINE2]+Chr(13) & Chr(10)+[NCPADDLINE4]

 

[vbaInet]

Thanks.

This is what I see.

 

[boblarson]

Thanks.

This is what I see.

Yeah, so did I, but now go into the table (AHIS) and remove the second line.

 

[vbaInet]

I see what you mean. I didn't think the OP was worried about the first line because from his first post it was impressioned that only Address 3 was the problem field.

How about this:

=LTrim([NCPADDLINE1]+Chr(13)+Chr(10)) & LTrim([NCPADDLINE2]+Chr(13)+Chr(10)) & [NCPADDLINE4]

 

[boblarson]

I think I've got it:

=([NCPADDLINE1]+Chr(13)+Chr(10)) & ([NCPADDLINE2]+Chr(13)+Chr(10)) & [NCPADDLINE4]

And I guess I see you posted the same.

 

[vbaInet]

Pretty much the same. But if 1 or 2 is zls, then it won't work, hence the LTrim()

 

[boblarson]

Ah, good point. I would change the table so the fields can't have Zero Length Strings. In the original table the first field was set to NO for ZLS but the other two were set to YES for some unknown reason.