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
Solved

Eliminatign Blank Lines in Address on Letter, Can Shrink

Posted on 2014-01-14
11
426 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 35

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

 
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 35

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
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…

809 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