How to "skip a blank field" when combining text

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sue Compelling - 12 Nov 2005 03:54 GMT

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

Reply to this Message

Allen Browne - 12 Nov 2005 04:31 GMT

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) & ...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi, context is ... my app is a Contact Centre Competency inventory. Each
> competency is assessed as Essential or Desired [for each role] and each
[quoted text clipped - 39 lines]
>
> Sue Compelling

Reply to this Message

Sue Compelling - 12 Nov 2005 04:56 GMT

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

Signature

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:
[quoted text clipped - 49 lines]
> >
> > Sue Compelling

Reply to this Message

Allen Browne - 12 Nov 2005 06:23 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen - thanks for replying so quickly ...
>
[quoted text clipped - 81 lines]
>> > at
>> > ACCESS.

Reply to this Message

Sue Compelling - 12 Nov 2005 06:42 GMT

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

Signature

Sue Compelling

> Whate you have done looks right. If it is not working, we need to trace what
> data is acually in these fields.
[quoted text clipped - 94 lines]
> >> > at
> >> > ACCESS.

Reply to this Message

Allen Browne - 12 Nov 2005 06:48 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Hi Allen
>
[quoted text clipped - 128 lines]
>> >> > at
>> >> > ACCESS.

Reply to this Message

Sue Compelling - 12 Nov 2005 07:26 GMT

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

Signature

Sue Compelling

> Yes, 0 is false; -1 is True to Access.
>
[quoted text clipped - 139 lines]
> >> >> > at
> >> >> > ACCESS.

Reply to this Message