Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Eliminatign Blank Lines in Address on Letter, Can Shrink

Posted on 2014-01-14
11
Medium Priority
?
431 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 39

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
Industry Leaders: 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 39

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 2000 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 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