Ignore nulls in concatenation

[jmoore]

This is what I have in the control source for a text box in a report. It
does not display any results and I wonder if it is because there could be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] & Chr(13) &
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) & [A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) & Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) & [ASectionComments]

Thanks.

 

[Michel Walsh]

I suppose it does display something, a lot of empty lines. Can you 'scroll'
down to see if your text is not under the visible section of your window?

You can avoid those empty lines with something like:

=( [A1aC] + Chr(13) + Chr(10) ) & ( [A1bC] + Chr(13) + Chr(10) ) & ( [A2C] +
Chr(13) + Char(10)) & ( . + . + . ) & ... & ( [A7C] + Chr(13) + Chr(10)) &
[ASectionComments]




Vanderghast, Access MVP

 

[Brett Stone]

bearing in mind that if any set of fields contains a null the result for the
concatenation will be null you probably should wrap each field in
nz([fieldname],"") instead
-Brett

I suppose it does display something, a lot of empty lines. Can you 'scroll'
down to see if your text is not under the visible section of your window?

You can avoid those empty lines with something like:

=( [A1aC] + Chr(13) + Chr(10) ) & ( [A1bC] + Chr(13) + Chr(10) ) & ( [A2C] +
Chr(13) + Char(10)) & ( . + . + . ) & ... & ( [A7C] + Chr(13) + Chr(10)) &
[ASectionComments]




Vanderghast, Access MVP

This is what I have in the control source for a text box in a report. It
does not display any results and I wonder if it is because there could be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] & Chr(13)
&
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) & [A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) &
Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) & [ASectionComments]

Thanks.

 

[John Spencer]

That expression will should show multiple lines. If A1aC is null then
you will have a first line that shows blank and if A1bC is null then
your will have two blank lines.

You might consider using the following. It will eliminate the line feed
if the value is null.

=([A1aC] + Chr(13) + Chr(10)) & ([A1bC] + Chr(13) + Chr(10)) &
([A2C] + Chr(13) + Chr(10)) & ([A3C] + Chr(13) + Chr(10)) &
([A4C] + Chr(13) + Chr(10)) & ([A5aC] + Chr(13) + Chr(10)) &
([A5bC] + Chr(13) + Chr(10)) & ([A5cC] + Chr(13) + Chr(10)) &
([A6C] + Chr(13) + Chr(10)) & ([A7C] + Chr(13) + Chr(10)) &
[ASectionComments]

That relies on the different ways the & and + concatenate strings.

A + B + Null returns Null
A & B & Null returns A B
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Michel Walsh]

That is why I played with + and &. The + propagates the null, while the &
does NOT propagate it (kind of having CONCAT_NULL_YILEDS_NULL on / off
available in the same SQL statement).


Vanderghast, Access MVP

bearing in mind that if any set of fields contains a null the result for
the
concatenation will be null you probably should wrap each field in
nz([fieldname],"") instead
-Brett

I suppose it does display something, a lot of empty lines. Can you
'scroll'
down to see if your text is not under the visible section of your window?

You can avoid those empty lines with something like:

=( [A1aC] + Chr(13) + Chr(10) ) & ( [A1bC] + Chr(13) + Chr(10) ) & (
[A2C] +
Chr(13) + Char(10)) & ( . + . + . ) & ... & ( [A7C] + Chr(13) +
Chr(10)) &
[ASectionComments]




Vanderghast, Access MVP

 

[jmoore]

John, Thanks for your reply. However, I still have the same problem - a
blank line for each null field. It occurs to me that I may have posted this
to the wrong section. I am using this in a text box source control of a
report with can grow/shrink set to yes. Any other thoughts are much
appreciated. Otherwise I will have to publish this with Word and delete the
blank lines.

Thanks,
Joan

That expression will should show multiple lines. If A1aC is null then
you will have a first line that shows blank and if A1bC is null then
your will have two blank lines.

You might consider using the following. It will eliminate the line feed
if the value is null.

=([A1aC] + Chr(13) + Chr(10)) & ([A1bC] + Chr(13) + Chr(10)) &
([A2C] + Chr(13) + Chr(10)) & ([A3C] + Chr(13) + Chr(10)) &
([A4C] + Chr(13) + Chr(10)) & ([A5aC] + Chr(13) + Chr(10)) &
([A5bC] + Chr(13) + Chr(10)) & ([A5cC] + Chr(13) + Chr(10)) &
([A6C] + Chr(13) + Chr(10)) & ([A7C] + Chr(13) + Chr(10)) &
[ASectionComments]

That relies on the different ways the & and + concatenate strings.

A + B + Null returns Null
A & B & Null returns A B
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

This is what I have in the control source for a text box in a report. It
does not display any results and I wonder if it is because there could be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] & Chr(13) &
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) & [A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) & Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) & [ASectionComments]

Thanks.

 

[jmoore]

Brett, I am not familiar with this function. Could you please post an
example. Do I still use the Chr(13)&Chr(10)? I've tried all of the other
suggestions and the results still display a blank line for each null field.
Also, I realized that I may have posted this to the wrong group. I am using
this in the source field of a report text box. Does this make a difference?
Should I post a new question in the reports? Thanks.

bearing in mind that if any set of fields contains a null the result for the
concatenation will be null you probably should wrap each field in
nz([fieldname],"") instead
-Brett

I suppose it does display something, a lot of empty lines. Can you 'scroll'
down to see if your text is not under the visible section of your window?

You can avoid those empty lines with something like:

=( [A1aC] + Chr(13) + Chr(10) ) & ( [A1bC] + Chr(13) + Chr(10) ) & ( [A2C] +
Chr(13) + Char(10)) & ( . + . + . ) & ... & ( [A7C] + Chr(13) + Chr(10)) &
[ASectionComments]




Vanderghast, Access MVP

This is what I have in the control source for a text box in a report. It
does not display any results and I wonder if it is because there could be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] & Chr(13)
&
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) & [A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) &
Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) & [ASectionComments]

Thanks.

 

[jmoore]

Brett, I am not familiar with this. Could you please post an example. Do I
still use the Chr(13)&Chr(10)? I also noticed that I posted this to the
wrong group as I am using this in the control source of a report text box.
Thanks.

bearing in mind that if any set of fields contains a null the result for the
concatenation will be null you probably should wrap each field in
nz([fieldname],"") instead
-Brett

I suppose it does display something, a lot of empty lines. Can you 'scroll'
down to see if your text is not under the visible section of your window?

You can avoid those empty lines with something like:

=( [A1aC] + Chr(13) + Chr(10) ) & ( [A1bC] + Chr(13) + Chr(10) ) & ( [A2C] +
Chr(13) + Char(10)) & ( . + . + . ) & ... & ( [A7C] + Chr(13) + Chr(10)) &
[ASectionComments]




Vanderghast, Access MVP

This is what I have in the control source for a text box in a report. It
does not display any results and I wonder if it is because there could be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] & Chr(13)
&
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) & [A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) &
Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) & [ASectionComments]

Thanks.

 

[jmoore]

Brett, I am not familiar with this. Could you please post an example. Do I
still use the Chr(13)&Chr(10)? I also noticed that I posted this to the
wrong group as I am using this in the control source of a report text box.

bearing in mind that if any set of fields contains a null the result for the
concatenation will be null you probably should wrap each field in
nz([fieldname],"") instead
-Brett

I suppose it does display something, a lot of empty lines. Can you 'scroll'
down to see if your text is not under the visible section of your window?

You can avoid those empty lines with something like:

=( [A1aC] + Chr(13) + Chr(10) ) & ( [A1bC] + Chr(13) + Chr(10) ) & ( [A2C] +
Chr(13) + Char(10)) & ( . + . + . ) & ... & ( [A7C] + Chr(13) + Chr(10)) &
[ASectionComments]




Vanderghast, Access MVP

This is what I have in the control source for a text box in a report. It
does not display any results and I wonder if it is because there could be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] & Chr(13)
&
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) & [A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) &
Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) & [ASectionComments]

Thanks.

 

[Michel Walsh]

If field A1aC is filled with spaces, rather than being null, that may
produce what you said.




=(iif(0=len(trim("" & [A1aC])), null, a1ac) + Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A1bC])),null, a1bc)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A2C])),null, a2c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A3C] )),null, a3c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A4C])),null, a4c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A5aC] )),null, a5ac)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A5bC] )),null, a5bc)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A5cC] )),null, a5cc)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A6C] )),null, a6c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A7C] )),null, a7c)+ Chr(13) + Chr(10)) &
[ASectionComments]





Vanderghast, Access MVP

John, Thanks for your reply. However, I still have the same problem - a
blank line for each null field. It occurs to me that I may have posted
this
to the wrong section. I am using this in a text box source control of a
report with can grow/shrink set to yes. Any other thoughts are much
appreciated. Otherwise I will have to publish this with Word and delete
the
blank lines.

Thanks,
Joan

That expression will should show multiple lines. If A1aC is null then
you will have a first line that shows blank and if A1bC is null then
your will have two blank lines.

You might consider using the following. It will eliminate the line feed
if the value is null.

=([A1aC] + Chr(13) + Chr(10)) & ([A1bC] + Chr(13) + Chr(10)) &
([A2C] + Chr(13) + Chr(10)) & ([A3C] + Chr(13) + Chr(10)) &
([A4C] + Chr(13) + Chr(10)) & ([A5aC] + Chr(13) + Chr(10)) &
([A5bC] + Chr(13) + Chr(10)) & ([A5cC] + Chr(13) + Chr(10)) &
([A6C] + Chr(13) + Chr(10)) & ([A7C] + Chr(13) + Chr(10)) &
[ASectionComments]

That relies on the different ways the & and + concatenate strings.

A + B + Null returns Null
A & B & Null returns A B
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

This is what I have in the control source for a text box in a report.
It
does not display any results and I wonder if it is because there could
be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] &
Chr(13) &
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) &
[A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) &
Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) &
[ASectionComments]

Thanks.

 

[jmoore]

Thanks for taking the time to write all of this. Still no luck. I'll have
to give up for now and get the report done with Word.

If field A1aC is filled with spaces, rather than being null, that may
produce what you said.




=(iif(0=len(trim("" & [A1aC])), null, a1ac) + Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A1bC])),null, a1bc)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A2C])),null, a2c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A3C] )),null, a3c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A4C])),null, a4c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A5aC] )),null, a5ac)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A5bC] )),null, a5bc)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A5cC] )),null, a5cc)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A6C] )),null, a6c)+ Chr(13) + Chr(10)) &
(iif(0=len(trim("" & [A7C] )),null, a7c)+ Chr(13) + Chr(10)) &
[ASectionComments]





Vanderghast, Access MVP

John, Thanks for your reply. However, I still have the same problem - a
blank line for each null field. It occurs to me that I may have posted
this
to the wrong section. I am using this in a text box source control of a
report with can grow/shrink set to yes. Any other thoughts are much
appreciated. Otherwise I will have to publish this with Word and delete
the
blank lines.

Thanks,
Joan

That expression will should show multiple lines. If A1aC is null then
you will have a first line that shows blank and if A1bC is null then
your will have two blank lines.

You might consider using the following. It will eliminate the line feed
if the value is null.

=([A1aC] + Chr(13) + Chr(10)) & ([A1bC] + Chr(13) + Chr(10)) &
([A2C] + Chr(13) + Chr(10)) & ([A3C] + Chr(13) + Chr(10)) &
([A4C] + Chr(13) + Chr(10)) & ([A5aC] + Chr(13) + Chr(10)) &
([A5bC] + Chr(13) + Chr(10)) & ([A5cC] + Chr(13) + Chr(10)) &
([A6C] + Chr(13) + Chr(10)) & ([A7C] + Chr(13) + Chr(10)) &
[ASectionComments]

That relies on the different ways the & and + concatenate strings.

A + B + Null returns Null
A & B & Null returns A B
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


jmoore wrote:
This is what I have in the control source for a text box in a report.
It
does not display any results and I wonder if it is because there could
be
many nulls in these fields. Is there a way to have nulls ignored?

=[A1aC] & Chr(13) & Chr(10) & [A1bC] & Chr(13) & Chr(10) & [A2C] &
Chr(13) &
Chr(10) & [A3C] & Chr(13) & Chr(10) & [A4C] & Chr(13) & Chr(10) &
[A5aC] &
Chr(13) & Chr(10) & [A5bC] & Chr(13) & Chr(10) & [A5cC] & Chr(13) &
Chr(10) &
[A6C] & Chr(13) & Chr(10) & [A7C] & Chr(13) & Chr(10) &
[ASectionComments]

Thanks.