Empty fields in report

[Mary Ann]

I have a database of contact names and addresses. The address is entered in
the table in a series of Text data type fields e.g. Add1, Add2, Add3, Add4,
Town, County, Postcode.

Not all contacts have data in e.g. Add3 or Add4.

I want to create a report with names and addresses. I have laid out the
address fields underneath each other. However, there is an empty space for
those contacts who do not have data in all the fields.

How can I ensure the address does not leave empty lines in the report,
obviously on a contact by contact basis?

I am a sophisticated user of Access but do not do programming.

I am using Access 2003.

 

[John Spencer]

You can try using Setting the Can Shrink properties of the controls and
section to Yes (True). If there are no other cotntrols on the same
horizontal line, then that should take care of the problem. Otherwise you
may have to build the entire address and put it into one control

Otherwise, you can try concatenating the address fields into one for
purposes of the report. You can do that in the query or in a control on the
report.

Add1 & IIF(IsNull(Add2),"",chr(13) & Chr(10) & Add2) &
IIF(IsNull(Add3),"",chr(13) & Chr(10) & Add3) & IIF(IsNull(Add4),"",chr(13)
& Chr(10) & Add4) & Chr(13) & Chr(10) & Town & " " & County & " " & PostCode

You can also use the fact that Access concatenates strings/nulls
differently with the & and + operators. The & treats nulls as zero-length
strings, while + propagates the null to the entire string. For example: "A"
& Null & "B" returns "AB", while "A" + Null + "B" returns Null.

Add1 & (Chr(13) + Chr(10) + Add2) & (Chr(13) + Chr(10) + Add3) & (Chr(13) +
Chr(10) + Add4) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Town & " " &
County & " " & PostCode

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mary Ann]

The IIF option works a treat – thank you!
I imagine Chr(13) and Chr(10) are what force a new line – what is the
difference between them?

 

[John Spencer]

Chr(13) is a carriage return
Chr(10) is a line feed

You need both and in that specific order to get a new line in Access.

In the old days (teletype machines, line printers, ...) you could send a
line feed to move the paper up and send a carriage return when you wanted to
get to the start of a line. So if you needed to move down the page 3 lines
and then start a line, you sent Line feed (3 times) and carriage return (one
time).

If you were doing a column of single digits, you could use line feed, back
space, character to print, repeat until you were done with the column.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Mary Ann]

That's the value of experience! Thank you John

Mary Ann