Solved

Eliminatign Blank Lines in Address on Letter, Can Shrink

Posted on 2014-01-14
11
429 Views
Last Modified: 2014-01-17
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
0
Comment
Question by:mlcktmguy
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39780044
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
 
LVL 37

Expert Comment

by:PatHartman
ID: 39780049
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39780183
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:mlcktmguy
ID: 39783573
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39783643
seen the first line of my post at http:#a39780044 ?
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39783648
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
 
LVL 29

Expert Comment

by:IrogSinta
ID: 39783653
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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39788819
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
 
LVL 1

Author Comment

by:mlcktmguy
ID: 39788832
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 39788836
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
 
LVL 1

Author Closing Comment

by:mlcktmguy
ID: 39788940
Thanks, exactly what I needed given the data that I had to work with.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question