Eliminatign Blank Lines in Address on Letter, Can Shrink

There are several lines available for addresses in our database.

Name
Addr1
Addr2
Addr3
CityStZip

Not all, in fact very few use all of the address lines but when layout the letters being mailed I don't want any blank lines.

In the past I know I have accomplished this by setting the 'CanShrink' property to 'Yes' on the report fields.  I have set the can shrink property on these fields to yes but the blank records are still showing.  

Are there any intricacies of the can shrink that I may not be complying with?

Also, assuming I can get the canshrink to work on the address headings are the only object on the report line like this:

Name
Addr1
Addr2
Addr3
CityStZip

Would it also work when there are other objects on the same line, like this


Name1                                                             Outstanding Balance
Addr1                                                               Owner Name
Addr2                                                               GPS Coodinates

Thanks you
LVL 1
mlcktmguyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
OneLineAddr:IIf([OwnerName] <>"",[ OwnerName],"") &
IIf([MailingAdd1] <>"",Chr(13) & Chr(10) & [MailingAdd1],"") &
IIf([MailingAddr2] <>"",Chr(13) & Chr(10) & [MailingAddr2],"") & 
IIf([MailingAddr3] <>"",Chr(13) & Chr(10) & [MailingAddr3],"")  &
IIf([MailingCityStZip] <>"",Chr(13) & Chr(10) & [MailingCityStZip],"")

or


OneLineAddr:IIf([OwnerName] & ""<>"",[ OwnerName],"") &
IIf([MailingAdd1] & ""<>"",Chr(13) & Chr(10) & [MailingAdd1],"") &
IIf([MailingAddr2] & ""<>"",Chr(13) & Chr(10) & [MailingAddr2],"") & 
IIf([MailingAddr3] & ""<>"",Chr(13) & Chr(10) & [MailingAddr3],"")  &
IIf([MailingCityStZip] & ""<>"",Chr(13) & Chr(10) & [MailingCityStZip],"")


.
0
 
Rey Obrero (Capricorn1)Commented:
i normally use one textbox control for the address and use an expression like this in a query

Address: IIf([Addr1] & ""<>"",[Addr1],"") & IIf([Addr2] & ""<>"",Chr(13) & Chr(10) & [addr2],"") & IIf([addr3] & ""<>"",Chr(13) & Chr(10) & [addr3],"")
0
 
PatHartmanCommented:
You need to set the can shrink/can grow properties to Yes for BOTH the controls and the section that contains them.  You also need to make sure that the controls don't overlap.  Shrink and grow won't work if any controls are overlapping.

If the controls still don't shrink, perhaps they are not really empty.  Make sure the empty fields are Null and NOT ZLS (zero length strings).

No, shrinking won't work in the last scenario since shrining shrinks an entire line and the line isn't empty.  If you want an address block to shrink independently, you need to concatenate the fields.

FirstName & " " & LastName & vbCrLf & (Address + vbCrLf) & (Address2 + vbCrLf) & City & ", " & State & " " & Zip

Notice that some operators are & and some are +.  That is intentional since the two operators have different properties when it comes to null values.  The & ignores nulls but the + will return null when one of the two values is null so if you concatenate a null address with a CrLf using the & you still get the CrLf but if you use the +, you get nothing so essentially the line doesn't exist.
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
IrogSintaCommented:
You can also add the formula inside a textbox.   At the end of each line (after each single quote), add a carriage return using CTRL+ENTER.  The + sign concatenates the fields and if the field is null it disregards the carriage return along with the null field.  So if you the only fields with data were Name, State, and Zip, it would show up as:

John Smith
NY 12345


= [Name] & '
'+ [Addr1] & '
'+ [Addr2] & '
'+ [Addr3] & '
'+ [City] + ', ' & [State] + '  ' & [Zip]

You can learn more about this concatenation style form harfang's article:
Concatenation in Access Reports (and Forms)
0
 
mlcktmguyAuthor Commented:
The concatenation scheme seems to be the way to go.  I assume when you use that you only have one text box on the screen, tall enough to hold the entire address rather than 6 textboxes on top of each other, 1 for each address line
0
 
Rey Obrero (Capricorn1)Commented:
seen the first line of my post at http:#a39780044 ?
0
 
PatHartmanCommented:
That is correct.  I usually do the concatenation in the query but you could do it in the ControlSource of a control also or even in code.
0
 
IrogSintaCommented:
Yes it's just one text box for the Name and Address.  Then you can also have another text box for  Outstanding Balance, Owner Name, and GPS Coodinates as mentioned in your question.
0
 
mlcktmguyAuthor Commented:
Found out the fields are not null, if balnk they contain "".  I am trying to implement capicorn1's suggestion inside a query.  This is exactly what I'm trying to use:

OneLineAddr:IIf([OwnerName] <>"",[ OwnerName],"") & 
IIf([MailingAdd1] <>"",Chr(13) & Chr(10) & [MailingAdd1],"") & 
IIf([MailingAddr2] <>"",Chr(13) & Chr(10) & [MailingAddr2],"")
IIf([MailingAddr3] <>"",Chr(13) & Chr(10) & [MailingAddr3],"")
IIf([MailingCityStZip] <>"",Chr(13) & Chr(10) & [MailingCityStZip],"")

I'm getting Invalid syntax.  You may have entered an operand, such as the + operator, in an expression without a corresponding operand.

Can anyone spot my issue?  I've tried several variations including replacing Chr(13) & Chr(10) with vbCrLF but can't get rid of this error.
0
 
mlcktmguyAuthor Commented:
Found it:  Missing &'s at the end of the thrid and fourth lines
OneLineAddr: IIf([OwnerName]<>"",[OwnerName],"") & IIf([MailingAdd1]<>"",Chr(13) & Chr(10) & [MailingAdd1],"") & IIf([MailingAddr2]<>"",Chr(13) & Chr(10) & [MailingAddr2],"") & IIf([MailingAddr3]<>"",Chr(13) & Chr(10) & [MailingAddr3],"") & IIf([MailingCityStZip]<>"",Chr(13) & Chr(10) & [MailingCityStZip],"")
0
 
mlcktmguyAuthor Commented:
Thanks, exactly what I needed given the data that I had to work with.
0
All Courses

From novice to tech pro — start learning today.