How to "skip a blank field" when combining text

G

Guest

Hi, context is ... my app is a Contact Centre Competency inventory. Each
competency is assessed as Essential or Desired [for each role] and each
individual's performance is assessed as Low or High. I'm developing
reporting to display this data into a quadrant grid.

For each quadrant I've wriiten a statement that draws ALL the competencies
together where the agent was considered Low, then the next quadrant draws on
all the competencies where the agent was considered High etc ...

My problem is I'm trying to have all these fields display sequentially [with
an extra line between each for ease of reading], though if a competency is
not appropriate for this quadrant then the statement is leaving a blank row
for the "empty competency" AND a blank row for readibility [as per my code].
How do I get rid of the blank spaces?

Quadrant Statement is:

=[NegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NgaL] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) & [ResuL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[ResiL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RespL] & Chr(13) & Chr(10)
& Chr(13) & Chr(10) & [LegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [TTPL]
& Chr(13) & Chr(10) & Chr(13) & Chr(10) & [CasL] & Chr(13) & Chr(10) &
Chr(13) & Chr(10) & [EnfL]


NegL field [and all others] is derived from this type of statement:

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through tactical
engagement","")

Really appreciate any help you can give. I'm relatively "low competent" at
ACCESS.

TIA

Sue Compelling
 
A

Allen Browne

Rather than use a convoluted combination of IsNull() and IIf(), you can take
advantage of a subtle difference between the 2 concatenation operators:
"A" & Null => "A"
"A" + Null => Null

If you use plus between the field and the CrLf, the carriage return
characters disappear if the field is null. Then use ampersand for the next
field. This kind of thing:
=[NegL]+Chr(13)+Chr(10) & [NegL]+Chr(13)+Chr(10) & ...
 
G

Guest

Hi Allen - thanks for replying so quickly ...

I've changed the statement to the following: [I've included an extra
carriage return as I want to have a line space between each competency]. The
first four competencies were empty though the text still displays as eight
empty lines and then the next four fields .

=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[NgaL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResuL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResiL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[RespL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[LegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[TTPL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[CasL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [EnfL]

When I took out the extra carriage return it still had FOUR blank lines and
then the list of competencies.

Is there a different way I should be deriving the competency field?

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through tactical
engagement","")

Thanks Sue
--
Sue Compelling


Allen Browne said:
Rather than use a convoluted combination of IsNull() and IIf(), you can take
advantage of a subtle difference between the 2 concatenation operators:
"A" & Null => "A"
"A" + Null => Null

If you use plus between the field and the CrLf, the carriage return
characters disappear if the field is null. Then use ampersand for the next
field. This kind of thing:
=[NegL]+Chr(13)+Chr(10) & [NegL]+Chr(13)+Chr(10) & ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
Hi, context is ... my app is a Contact Centre Competency inventory. Each
competency is assessed as Essential or Desired [for each role] and each
individual's performance is assessed as Low or High. I'm developing
reporting to display this data into a quadrant grid.

For each quadrant I've wriiten a statement that draws ALL the competencies
together where the agent was considered Low, then the next quadrant draws
on
all the competencies where the agent was considered High etc ...

My problem is I'm trying to have all these fields display sequentially
[with
an extra line between each for ease of reading], though if a competency is
not appropriate for this quadrant then the statement is leaving a blank
row
for the "empty competency" AND a blank row for readibility [as per my
code].
How do I get rid of the blank spaces?

Quadrant Statement is:

=[NegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NgaL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [ResuL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[ResiL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RespL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [LegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[TTPL]
& Chr(13) & Chr(10) & Chr(13) & Chr(10) & [CasL] & Chr(13) & Chr(10) &
Chr(13) & Chr(10) & [EnfL]


NegL field [and all others] is derived from this type of statement:

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through tactical
engagement","")

Really appreciate any help you can give. I'm relatively "low competent"
at
ACCESS.

TIA

Sue Compelling
Click to expand...
Click to expand...
 
A

Allen Browne

Whate you have done looks right. If it is not working, we need to trace what
data is acually in these fields.

Let's start with just 2 fields, so the Control Source is:
=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [NgaL]

Add another text box with Control Source of:
=([NegL] Is Null)

If the 2nd box returns True when you can't see anything in NegL, then there
should be no line break ahead of NgaL. If it returns False, then NegL does
contains a value--perhaps a space or a zero-length string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
Hi Allen - thanks for replying so quickly ...

I've changed the statement to the following: [I've included an extra
carriage return as I want to have a line space between each competency].
The
first four competencies were empty though the text still displays as eight
empty lines and then the next four fields .

=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[NgaL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResuL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResiL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[RespL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[LegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[TTPL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[CasL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [EnfL]

When I took out the extra carriage return it still had FOUR blank lines
and
then the list of competencies.

Is there a different way I should be deriving the competency field?

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through tactical
engagement","")

Thanks Sue
--
Sue Compelling


Allen Browne said:
Rather than use a convoluted combination of IsNull() and IIf(), you can
take
advantage of a subtle difference between the 2 concatenation operators:
"A" & Null => "A"
"A" + Null => Null

If you use plus between the field and the CrLf, the carriage return
characters disappear if the field is null. Then use ampersand for the
next
field. This kind of thing:
=[NegL]+Chr(13)+Chr(10) & [NegL]+Chr(13)+Chr(10) & ...


message
Hi, context is ... my app is a Contact Centre Competency inventory.
Each
competency is assessed as Essential or Desired [for each role] and each
individual's performance is assessed as Low or High. I'm developing
reporting to display this data into a quadrant grid.

For each quadrant I've wriiten a statement that draws ALL the
competencies
together where the agent was considered Low, then the next quadrant
draws
on
all the competencies where the agent was considered High etc ...

My problem is I'm trying to have all these fields display sequentially
[with
an extra line between each for ease of reading], though if a competency
is
not appropriate for this quadrant then the statement is leaving a blank
row
for the "empty competency" AND a blank row for readibility [as per my
code].
How do I get rid of the blank spaces?

Quadrant Statement is:

=[NegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NgaL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [ResuL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[ResiL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RespL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [LegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[TTPL]
& Chr(13) & Chr(10) & Chr(13) & Chr(10) & [CasL] & Chr(13) & Chr(10) &
Chr(13) & Chr(10) & [EnfL]


NegL field [and all others] is derived from this type of statement:

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through
tactical
engagement","")

Really appreciate any help you can give. I'm relatively "low
competent"
at
ACCESS.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Hi Allen

OK [NegL] field displays blank

NegL is derived from =IIf([Neg]="L","Negotiation; achieving suitable
outcomes through tactical engagement","")

Text box [Allen] displays 0

Allen is derived from =([NegL] Is Null)

Does 0 mean false and 1 mean True? - and if so - what do I do now?

Cheers

Sue

--
Sue Compelling


Allen Browne said:
Whate you have done looks right. If it is not working, we need to trace what
data is acually in these fields.

Let's start with just 2 fields, so the Control Source is:
=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [NgaL]

Add another text box with Control Source of:
=([NegL] Is Null)

If the 2nd box returns True when you can't see anything in NegL, then there
should be no line break ahead of NgaL. If it returns False, then NegL does
contains a value--perhaps a space or a zero-length string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
Hi Allen - thanks for replying so quickly ...

I've changed the statement to the following: [I've included an extra
carriage return as I want to have a line space between each competency].
The
first four competencies were empty though the text still displays as eight
empty lines and then the next four fields .

=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[NgaL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResuL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResiL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[RespL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[LegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[TTPL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[CasL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [EnfL]

When I took out the extra carriage return it still had FOUR blank lines
and
then the list of competencies.

Is there a different way I should be deriving the competency field?

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through tactical
engagement","")

Thanks Sue
--
Sue Compelling


Allen Browne said:
Rather than use a convoluted combination of IsNull() and IIf(), you can
take
advantage of a subtle difference between the 2 concatenation operators:
"A" & Null => "A"
"A" + Null => Null

If you use plus between the field and the CrLf, the carriage return
characters disappear if the field is null. Then use ampersand for the
next
field. This kind of thing:
=[NegL]+Chr(13)+Chr(10) & [NegL]+Chr(13)+Chr(10) & ...


message
Hi, context is ... my app is a Contact Centre Competency inventory.
Each
competency is assessed as Essential or Desired [for each role] and each
individual's performance is assessed as Low or High. I'm developing
reporting to display this data into a quadrant grid.

For each quadrant I've wriiten a statement that draws ALL the
competencies
together where the agent was considered Low, then the next quadrant
draws
on
all the competencies where the agent was considered High etc ...

My problem is I'm trying to have all these fields display sequentially
[with
an extra line between each for ease of reading], though if a competency
is
not appropriate for this quadrant then the statement is leaving a blank
row
for the "empty competency" AND a blank row for readibility [as per my
code].
How do I get rid of the blank spaces?

Quadrant Statement is:

=[NegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NgaL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [ResuL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[ResiL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RespL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [LegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
[TTPL]
& Chr(13) & Chr(10) & Chr(13) & Chr(10) & [CasL] & Chr(13) & Chr(10) &
Chr(13) & Chr(10) & [EnfL]


NegL field [and all others] is derived from this type of statement:

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through
tactical
engagement","")

Really appreciate any help you can give. I'm relatively "low
competent"
at
ACCESS.
Click to expand...
Click to expand...
Click to expand...
 
A

Allen Browne

Yes, 0 is false; -1 is True to Access.

Your IIf() expression is using a zero-length string for the False part.
Replace it with Null, i.e.:
=IIf([Neg]="L","Negotiation; achiev...", Null)

For an explanation of how Null behaves differently than a ZLS, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
Hi Allen

OK [NegL] field displays blank

NegL is derived from =IIf([Neg]="L","Negotiation; achieving suitable
outcomes through tactical engagement","")

Text box [Allen] displays 0

Allen is derived from =([NegL] Is Null)

Does 0 mean false and 1 mean True? - and if so - what do I do now?

Cheers

Sue

--
Sue Compelling


Allen Browne said:
Whate you have done looks right. If it is not working, we need to trace
what
data is acually in these fields.

Let's start with just 2 fields, so the Control Source is:
=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [NgaL]

Add another text box with Control Source of:
=([NegL] Is Null)

If the 2nd box returns True when you can't see anything in NegL, then
there
should be no line break ahead of NgaL. If it returns False, then NegL
does
contains a value--perhaps a space or a zero-length string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen - thanks for replying so quickly ...

I've changed the statement to the following: [I've included an extra
carriage return as I want to have a line space between each
competency].
The
first four competencies were empty though the text still displays as
eight
empty lines and then the next four fields .

=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[NgaL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResuL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResiL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[RespL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[LegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[TTPL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[CasL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [EnfL]

When I took out the extra carriage return it still had FOUR blank lines
and
then the list of competencies.

Is there a different way I should be deriving the competency field?

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through
tactical
engagement","")

Thanks Sue
--
Sue Compelling


:

Rather than use a convoluted combination of IsNull() and IIf(), you
can
take
advantage of a subtle difference between the 2 concatenation
operators:
"A" & Null => "A"
"A" + Null => Null

If you use plus between the field and the CrLf, the carriage return
characters disappear if the field is null. Then use ampersand for the
next
field. This kind of thing:
=[NegL]+Chr(13)+Chr(10) & [NegL]+Chr(13)+Chr(10) & ...


message
Hi, context is ... my app is a Contact Centre Competency inventory.
Each
competency is assessed as Essential or Desired [for each role] and
each
individual's performance is assessed as Low or High. I'm developing
reporting to display this data into a quadrant grid.

For each quadrant I've wriiten a statement that draws ALL the
competencies
together where the agent was considered Low, then the next quadrant
draws
on
all the competencies where the agent was considered High etc ...

My problem is I'm trying to have all these fields display
sequentially
[with
an extra line between each for ease of reading], though if a
competency
is
not appropriate for this quadrant then the statement is leaving a
blank
row
for the "empty competency" AND a blank row for readibility [as per
my
code].
How do I get rid of the blank spaces?

Quadrant Statement is:

=[NegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NgaL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [ResuL] & Chr(13) & Chr(10) & Chr(13) &
Chr(10) &
[ResiL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RespL] & Chr(13)
&
Chr(10)
& Chr(13) & Chr(10) & [LegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10)
&
[TTPL]
& Chr(13) & Chr(10) & Chr(13) & Chr(10) & [CasL] & Chr(13) & Chr(10)
&
Chr(13) & Chr(10) & [EnfL]


NegL field [and all others] is derived from this type of statement:

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through
tactical
engagement","")

Really appreciate any help you can give. I'm relatively "low
competent"
at
ACCESS.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Allen - you're brilliant. Thanks so much - the grid looks great.
--
Sue Compelling


Allen Browne said:
Yes, 0 is false; -1 is True to Access.

Your IIf() expression is using a zero-length string for the False part.
Replace it with Null, i.e.:
=IIf([Neg]="L","Negotiation; achiev...", Null)

For an explanation of how Null behaves differently than a ZLS, see:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sue Compelling said:
Hi Allen

OK [NegL] field displays blank

NegL is derived from =IIf([Neg]="L","Negotiation; achieving suitable
outcomes through tactical engagement","")

Text box [Allen] displays 0

Allen is derived from =([NegL] Is Null)

Does 0 mean false and 1 mean True? - and if so - what do I do now?

Cheers

Sue

--
Sue Compelling


Allen Browne said:
Whate you have done looks right. If it is not working, we need to trace
what
data is acually in these fields.

Let's start with just 2 fields, so the Control Source is:
=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [NgaL]

Add another text box with Control Source of:
=([NegL] Is Null)

If the 2nd box returns True when you can't see anything in NegL, then
there
should be no line break ahead of NgaL. If it returns False, then NegL
does
contains a value--perhaps a space or a zero-length string.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi Allen - thanks for replying so quickly ...

I've changed the statement to the following: [I've included an extra
carriage return as I want to have a line space between each
competency].
The
first four competencies were empty though the text still displays as
eight
empty lines and then the next four fields .

=[NegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[NgaL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResuL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[ResiL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[RespL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[LegL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[TTPL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) &
[CasL]+Chr(13)+Chr(10)+Chr(13)+Chr(10) & [EnfL]

When I took out the extra carriage return it still had FOUR blank lines
and
then the list of competencies.

Is there a different way I should be deriving the competency field?

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through
tactical
engagement","")

Thanks Sue
--
Sue Compelling


:

Rather than use a convoluted combination of IsNull() and IIf(), you
can
take
advantage of a subtle difference between the 2 concatenation
operators:
"A" & Null => "A"
"A" + Null => Null

If you use plus between the field and the CrLf, the carriage return
characters disappear if the field is null. Then use ampersand for the
next
field. This kind of thing:
=[NegL]+Chr(13)+Chr(10) & [NegL]+Chr(13)+Chr(10) & ...


message
Hi, context is ... my app is a Contact Centre Competency inventory.
Each
competency is assessed as Essential or Desired [for each role] and
each
individual's performance is assessed as Low or High. I'm developing
reporting to display this data into a quadrant grid.

For each quadrant I've wriiten a statement that draws ALL the
competencies
together where the agent was considered Low, then the next quadrant
draws
on
all the competencies where the agent was considered High etc ...

My problem is I'm trying to have all these fields display
sequentially
[with
an extra line between each for ease of reading], though if a
competency
is
not appropriate for this quadrant then the statement is leaving a
blank
row
for the "empty competency" AND a blank row for readibility [as per
my
code].
How do I get rid of the blank spaces?

Quadrant Statement is:

=[NegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [NgaL] & Chr(13) &
Chr(10)
& Chr(13) & Chr(10) & [ResuL] & Chr(13) & Chr(10) & Chr(13) &
Chr(10) &
[ResiL] & Chr(13) & Chr(10) & Chr(13) & Chr(10) & [RespL] & Chr(13)
&
Chr(10)
& Chr(13) & Chr(10) & [LegL] & Chr(13) & Chr(10) & Chr(13) & Chr(10)
&
[TTPL]
& Chr(13) & Chr(10) & Chr(13) & Chr(10) & [CasL] & Chr(13) & Chr(10)
&
Chr(13) & Chr(10) & [EnfL]


NegL field [and all others] is derived from this type of statement:

=IIf([Neg]="L","Negotiation; achieving suitable outcomes through
tactical
engagement","")

Really appreciate any help you can give. I'm relatively "low
competent"
at
ACCESS.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Create an Audit Trail of Record Changes in a Form 1

Top