Inserting a line break in a Access report text field when a certain condition is met

Question

Alright, to begin with, I am not an expert in using reports / forms in Access. The original Access file originates from Access 2000 and I am rebuilding it now to Access 2013, plus adding a few style changes including a line break in a my report.

At first I thought a line break wouldn't be to hard and that I could easily add it in a textbox with either or & Chr(13) & Chr(10) - also tried it with the $ after the Chr. This didn't worked though.

The problem / background

So I have got a report that shows some values that are retrieved from a query. These values are displayed in text boxes.

I have one hidden field that retrieves a single query value (relations) that is inserted in its control source, this seems to work.

The following thing I want to do is checking if the value is not empty, and if it isn't then output the value after a line break.

More background

This is being done for address information. Sometimes a relation has 2 address lines instead of just one, so if the relation has 2 lines then it needs to show both lines while if the relation only has one line it should show just one line (without a blank enter). To give an idea:

Relation A:

Company Name X 
Street Name and number
Zipcode

Relation B:

Company Name Y
Second Name Z 
Street Name and number
Zipcode

If I would insert both the company names separate, then you would get a hideous blank line break if the company doesn't have a second name.

So the textbox code is:

RelationName (retrieved from query)
RelationName2 (retrieved from query)
StreetName (retrieved from query)
Zipcode (retrieved from query)

And for company A it then would look like:

Company Name X 

Street Name and number
Zipcode

I thought I could fix this by adding a condition for the second name textbox:

=IIf([RelationName2]<>"",Chr(13) & Chr(10) & [RelationName2],"")

So if relation name 2 exists, then do a line break and output the second relation name else do nothing (leave empty).

In code this looks like (the {} are textboxes):

{RelationName}  {=IIf([RelationName2]<>"",Chr(13) & Chr(10) & [RelationName2],"")}   
{StreetName} 
{Zipcode}

The line breaks don't work though, and because of this I also cant test if the line break even works when the condition is met. Perhaps there are other ways to do it (instead of using line breaks). P.S. I also tried setting the textbox text format to rich text instead of plain text.

Answer 1

Try checking for Null and not empty string:

IIf(IsNull([RelationName2]), .., ..)

Answer 2

I know this an old post but someone might be helped by this.

I don't know the reason but the following worked for me:

Just switch characters 13 and 10 in your expression. That is:

=IIf([RelationName2]<>"",Chr(10) & Chr(13) & [RelationName2],"")