Problem with Reports

[Robbyp2001]

In my database there are 2 fields that are populated only by a limited number
of records. All the others are blank. On my report there is an area with
the headings 'PE Practical' and 'PE Theory'. What I would like to do is have
this box populated only when there is a value e.g 'PE Practical = 70' but
completely blank when there is no value. I have created a text box on the
report with SQL ="PE Practical" & " " & [PE Practical]. This works but the
headings remain even when the record is blank.

How do I get the report to show no headings when the record has no value and
headings when it has a value?

 

[Ken Sheridan]

Try:

=IIf(IsNull([PE Practical]), Null, "PE Practical" & " " & [PE Practical])

If [PE Practical] is Null then the IIf function will return Null, otherwise
the concatenated string expression.

Ken Sheridan
Stafford, England

 

[Rob2001]

Many thanks Ken, I'll certainly try this although I think I now have a
different solution: ="PE Practical " + [PE Practical]

This takes advantage of the fact that + propagates Nulls, while & doesn't
(in other words, "asd" & Null is "asd", while "asd" + Null is Null)

Try:

=IIf(IsNull([PE Practical]), Null, "PE Practical" & " " & [PE Practical])

If [PE Practical] is Null then the IIf function will return Null, otherwise
the concatenated string expression.

Ken Sheridan
Stafford, England

In my database there are 2 fields that are populated only by a limited number
of records. All the others are blank. On my report there is an area with
the headings 'PE Practical' and 'PE Theory'. What I would like to do is have
this box populated only when there is a value e.g 'PE Practical = 70' but
completely blank when there is no value. I have created a text box on the
report with SQL ="PE Practical" & " " & [PE Practical]. This works but the
headings remain even when the record is blank.

How do I get the report to show no headings when the record has no value and
headings when it has a value?

 

[Ken Sheridan]

The only reason I didn't suggest that was because I thought there might be a
possibility that the use of the + arithmetical operator might give rise to a
type mismatch error if PE Practical was a number data type, i.e. trying to
add a string expression to a number rather than concatenate them. If you
find it works, however, my suspicions were obviously unfounded.

Ken Sheridan
Stafford, England

Many thanks Ken, I'll certainly try this although I think I now have a
different solution: ="PE Practical " + [PE Practical]

This takes advantage of the fact that + propagates Nulls, while & doesn't
(in other words, "asd" & Null is "asd", while "asd" + Null is Null)

Try:

=IIf(IsNull([PE Practical]), Null, "PE Practical" & " " & [PE Practical])

If [PE Practical] is Null then the IIf function will return Null, otherwise
the concatenated string expression.

Ken Sheridan
Stafford, England

In my database there are 2 fields that are populated only by a limited number
of records. All the others are blank. On my report there is an area with
the headings 'PE Practical' and 'PE Theory'. What I would like to do is have
this box populated only when there is a value e.g 'PE Practical = 70' but
completely blank when there is no value. I have created a text box on the
report with SQL ="PE Practical" & " " & [PE Practical]. This works but the
headings remain even when the record is blank.

How do I get the report to show no headings when the record has no value and
headings when it has a value?

 

[Rob2001]

Thanks for that Ken. I'll do a double check to make sure that all the
records are OK. If not, I'll certainly use your solution.

Many thanks once again. You're a star!

Rob

The only reason I didn't suggest that was because I thought there might be a
possibility that the use of the + arithmetical operator might give rise to a
type mismatch error if PE Practical was a number data type, i.e. trying to
add a string expression to a number rather than concatenate them. If you
find it works, however, my suspicions were obviously unfounded.

Ken Sheridan
Stafford, England

Many thanks Ken, I'll certainly try this although I think I now have a
different solution: ="PE Practical " + [PE Practical]

This takes advantage of the fact that + propagates Nulls, while & doesn't
(in other words, "asd" & Null is "asd", while "asd" + Null is Null)

Try:

=IIf(IsNull([PE Practical]), Null, "PE Practical" & " " & [PE Practical])

If [PE Practical] is Null then the IIf function will return Null, otherwise
the concatenated string expression.

Ken Sheridan
Stafford, England

:

In my database there are 2 fields that are populated only by a limited number
of records. All the others are blank. On my report there is an area with
the headings 'PE Practical' and 'PE Theory'. What I would like to do is have
this box populated only when there is a value e.g 'PE Practical = 70' but
completely blank when there is no value. I have created a text box on the
report with SQL ="PE Practical" & " " & [PE Practical]. This works but the
headings remain even when the record is blank.

How do I get the report to show no headings when the record has no value and
headings when it has a value?