Concatenating 5 fields to one on report (1 Viewer)

[Design by Sue]

I have five reason fields in my table and I would like combine them into one field on my report, with line breaks between so each reason starts on it's on line. Also if there is a blank record, I would like the report not to display a blank line. I have found ways to do this online for 2 records but I need this for 5. Also when I do manage to get the lines all to show in the report there are 3 paragraph returns between them - making the reason display of the report 15 lines tall, not just 5. The code I have so far, which displays each of the reasons but spaced as described is:

=[REASON1] & " " & [REASON2] & " " & [REASON3] & " " & [REASON4] & " " & [REASON5]

Thanks!
Sue

 

[Design by Sue]

Found part of the problem - I need to add Trim to the fields - that cleared up the spacing problem. Still need help on the displaying on separate lines and not displaying line if nothing there. Will continue working on this and if I find the solution I will post it for others the future.

 

[Design by Sue]

Solved it by using the following code

=IIf(IsNull([REASON1]),"",Trim([REASON1]) & Chr(13)& Chr(10)) & IIf(IsNull([REASON2]),"",Trim([REASON2]) & Chr(13) & Chr(10)) & IIf(IsNull([REASON3]),"",Trim([REASON3]) & Chr(13) & Chr(10)) & IIf(IsNull([REASON4]),"",Trim([REASON4]) & Chr(13) & Chr(10)) & IIf(IsNull([REASON5]),"",Trim([REASON5]))

 

[Frothingslosh]

One question, why are you using enumerated fields rather than a proper 1:many relationship?

 

[Design by Sue]

Not sure what you mean by 1:many relationship in the instance.

 

[Frothingslosh]

In virtually every case where you use numbered fields, you're better off setting up a one-to-many relationship. In this case, you'd have a Reasons table linked to whatever this table is by the main table's primary key. That would allow you to just include a "Reason" control in the report, and each would show in a new line automatically.

Problems like what you just had to resolve are why we're always hammering at people here to normalize and not use numbered fields if it can POSSIBLY be provided.