report as letter (1 Viewer)

[bikermo]

* using access 2007

currently we build a customer list in access then use mail merge to put the fields into the letter in word. i'm having issues with the database being "in use" when i try to mail merge (it's in use by me, attempting to finish the mail merge) so i decided to try to create a report instead, but i don't know how to make the empty address fields not take up space.

for example, we've got the following:
Ship2Attention
Address1
Address2
Address3
Address4
City, State Zip

we will always have at least Address1 and Address2, but both Address3 and Address4 could be empty. i don't want a bunch of blank space between Address2 and City, State Zip if Address3 and Address4 are empty.

how do i do this? and please feel free to point me to the right previous thread if one exists, i haven't yet found the right search string apparently.

thanks a million-

 

[boblarson]

This should help:
http://support.microsoft.com/kb/287685

There is another way too, but I always forget which gets the + sign.

Something like this:

[AShip2Attention] & Chr(13) & Chr(10) &
[Address1] & Chr(13) & Chr(10) +
[Address2] & Chr(13) & Chr(10) +
[Address3] & Chr(13) & Chr(10) +
[Address4] & Chr(13) & Chr(10) &
[City] & ", " & [State] & " " & [Zip]

 

[bikermo]

thanks for the link, tried it and i get a syntax error. so then i tried your code and i'm still getting the blank lines. i searched for "Chr(13) & Chr(10)" and found something similar and when i tried that i still get the blank lines too.

i found a workaround where i make the last 3 fields (Address3, Addres4 & concatenated CityStateZip field) .01 in height, drop them almost on top of each other and set their "can grow" properties to "yes" but that leaves me with more space between the lines than i'd like. it works, so maybe i'll get over being picky!!

 

[Minddumps]

Not sure if this can help you figure out the spacing, but I was trying to figure out something similar. If field is blank than skip field basically. Though, all of mine were on one line and in a query.

NameFull: ([AllPOCs**].[Title] + " ") & [POC First] & " " & ([MI] + " ") & [POC Last]

the + " " inside the parentheses, says if field is blank/null don't use it. Perhaps adding the Chr 's inside parenthesis will pull off same effect in VBA??

Here's the link to the thread. http://www.access-programmers.co.uk/forums/showthread.php?t=251138

Good luck!