How can I ignore blank values when printing an address

[twiggymates]

Hello there

I have a report that prints an address of a customer, pulling each individual part of the address from the main table in the form Customer Name, Customer Address Line 1, Customer Address Line 2, Customer Address Line 3, Customer Postcode.

Sometimes several of the values can be blank and therefore when the report prints, it shows blank spaces in between, say for example, the name and postcode. Is there any way of ignoring blank lines completely and shifting up the next control to fill the space? I've tried can shrink but this seems only to work if all fields are pulled to one text box.

Cheers

 

[dhookom]

Can Shrink will work reliably as long as there are no controls to the left or right of the "can shrink" control. This only shrinks text boxes with Null values.

If you give us a little more info on your control sources, we might be able to provide more direction.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[BNPMike]

What you might want to do is clean up the data first. You can run a query which updates eg AddressLine2 to AddressLine3 if AddressLine2 is blank or null. Then repeat this for Line 1 and 2. You get the idea. A handful of simple queries will shuffle up the data to remove empty fields - all without VBA.

 

[lemerle]

Try this: Use the + NULL Property to Stop printing:

Use a Text Box, Can Shrink = True, Can Grow = True, Control Source = FixLine(" &quot & FixLine([Customer Name]) & (", " + Fixline([Customer Address Line 1])) & (", " + FixLine([Customer Address Line 2])) & (", " + Fixline([Customer Address Line 3])) & (" " + [Customer Post Code]))



Public Function FixLine(VarVal As Variant) As Variant
FixLine = VarVal + vbCrLf
End Function