Solved

Eliminatign Blank Lines in Address on Letter, Can Shrink

Posted on 2014-01-14
11
427 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
  • 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 36

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
Independent Software Vendors: 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 36

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 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