Concatenation of Multiple Fields into One that SHRINKS 2

[jdegeorge]

Hi:

I have a text field on a report that is based on the concatenated value of 4 other fields:

[tt]=[Owner1] & "
" & [Owner2] & "
" & [Owner3] & "
" & [Owner4][/tt]

Problem is that not all 4 fields will be populated, so I don't want the printed field to take up 4 lines. I started to put together a complicated "IF..." statement so that this field would be only 3 lines long if only 3 underlying fields were populated, etc., but it got out of hand.

Does anyone have any ideas? I'm lost. Thanks!

Jim DeGeorge

 

[dhookom]

There is nothing in your expression that would cause four lines. Could you share your actual expression?

You may simply need to introduce some "+" rather than "&".
Null + "Jim" = Null
Null & "Jim" = "Jim"
Chr(13) + Chr(10) + Null = Null

Duane
MS Access MVP

 

[jdegeorge]

dhookom

The "&" works well. They concatenate together, but even if one or more is blank the resulting field takes up 4 lines when printing. If "Owner 2" and "Owner 3" are blank, "" or null but "Owner 1" and "Owner 4" have values, I'd like only 2 lines of real estate to be used on my report.

I started to put together a series of nested If statements, but that got too complicated:

[tt]=If([Owner1]<>&quot;&quot;,[Owner1] & &quot;
&quot; & [Owner2] & &quot;
&quot; & [Owner3] & &quot;
&quot; & [Owner4],=If([Owner2]<>&quot;&quot;,[Owner2] & &quot;
&quot; & [Owner2] & &quot;
&quot; & [Owner4]) etc. etc.

This will get out of hand. Any ideas?


Jim DeGeorge

 

[dhookom]

How are you getting lines created when I don't see any expression that creates a carriage return/line feed. I would think that your entire expression would print in one very wide line with no new lines except for those created by word wrap. Are you providing all your details?

Duane
MS Access MVP

 

[jdegeorge]

dhookom

the & &quot;
&quot; & part is tantamount to the LINE FEED and CARRIAGE RETURN codes. It's just done with text rather than with code.

It would look like this had I used the codes:

[Owner1] & Chr(13) & Chr(10) & [Owner2] etc., etc.

Jim DeGeorge

 

[CosmoKramer]

Jim,

This syntax may look rather complicated, but it works well for me. Try something like this:

= IIf(IsNull(Me.[Owner1]), "", Me.[Owner1] & vbCrLf) & IIf(IsNull(Me.[Owner2]), "", Me.[Owner2] & vbCrLf) & IIf(IsNull(Me.[Owner3]), "", Me.[Owner3] & vbCrLf) & IIf(IsNull(Me.[Owner4]), "", Me.[Owner4] & vbCrLf)

Just make sure the Can Grow property is set to Yes.....




A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[jdegeorge]

CosmoKramer

How ingenious! It's a completely different approach. One question about IsNull...would I get a TRUE response if there were spaces in an OWNER field or does that not amount to NULL? I'll give this a try and let you know!

Jim DeGeorge

 

[jdegeorge]

Cosmo

It didn't like the ME part so I took that out, referring only to the fields in the query. I removed the last &quot;vbCrLf&quot; because that would never be needed. This was the resulting code:

[tt]=IIf(IsNull([Owner1]),&quot;&quot;,[Owner1] & vbCrLf) & IIf(IsNull([Owner2]),&quot;&quot;,[Owner2] & vbCrLf) & IIf(IsNull([Owner3]),&quot;&quot;,[Owner3] & vbCrLf) & IIf(IsNull([Owner4]),&quot;&quot;,[Owner4])[/tt]

Access didn't like this so it put every instance of &quot;vbCrLf&quot; in brackets, but it still asked me to define &quot;vbCrLf&quot;. I then tried to put quotes around &quot;vbCrLf&quot; and it added that those letters as part of the returned value.

Am I doing something wrong?

Jim DeGeorge

 

[CosmoKramer]

Sorry Jim, my bad.

Try setting the control source through the On Format event of the report section where this text box is, not in the text box itself:

Me.txtOwner = IIf(IsNull([Owner1]),"",[Owner1] & vbCrLf) & IIf(IsNull([Owner2]),"",[Owner2] & vbCrLf) & IIf(IsNull([Owner3]),"",[Owner3] & vbCrLf) & IIf(IsNull([Owner4]),"",[Owner4])

A common mistake that people make when trying to design something completely foolproof is to underestimate the ingenuity of complete fools.
Douglas Adams

 

[dhookom]

You can't use vbCrLf except in a module. You can use Chr(13)+Chr(10). By using &quot;+&quot; rather than &quot;&&quot;, you can effectively cancel part of your string. For instance:
Null + &quot;abc&quot; = Null
Null & &quot;abc&quot; = &quot;abc&quot;
Null + &quot;xyz&quot; & &quot;abc&quot; = &quot;abc&quot;
Null & &quot;xyz&quot; & &quot;abc&quot; = &quot;xyzabc&quot;

You can use this same &quot;feature&quot; to remove the CRLFs if a field value is null:
=[Owner1] + Chr(13) + Chr(10) & [Owner2] + Chr(13) + Chr(10)
& [Owner3] + Chr(13) + Chr(10) & [Owner4]
If [Owner1] is null then the first Chr(13)+Chr(10) are removed.

Duane
MS Access MVP

 

[jdegeorge]

Duane

You did it again! Enjoy the star, and thanks!

Jim DeGeorge

 

[jdegeorge]

Duane

You did it again! Enjoy the star, and thanks!

Cosmo

Thanks for your help, too!

Jim DeGeorge