Exclude Field from Report if Null 3

[MacroAlan]

My client has asked what I think is impossible to do easily. She wants to exclude a field and it's label from the report if the field is null.

It is easy to exclude the record if it is null.

Do we need to build the whole report in code and feed it based on fields that are "not null?"




Alan

 

[Crowley16]

can you not just set the visible option of the label or control?

--------------------
Procrastinate Now!

 

[dhookom]

This type of question quite often suggests an un-normalized table structure. However a Null value wouldn't show anything if it could. If you want the label to also hide, change the label control to a text box and set its control source to something like:
="Maiden Name: " + [MaidenName]
Make sure the text box is sized to not display the field value and don't allow it to grow.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rccline]

Just what I needed. Thanks Duane.

Robert

 

[dhookom]

rccline,
Thanks for searching!

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[rccline]

Hmmmm.... I try using this again on a new report and I get #Error.

If I set the control data source to
="Amt: "+ [Amt] the report displays #Error.

="Amt: "= [Amt] the report displays 0 even though the field contains a currency amount ($100).

Any suggestions?

Thanks.

Robert

 

[rccline]

Ahh ha!

Change the data source to:
="Amt: "& [Amt] and the report displays 100.

Now, how do you format that as currency ($100.00)?

 

[dhookom]

="Amt: "& Format([Amt],"currency")

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Prometheus123]

I did the same thing that rccline did and changed the + to a & but now my label is always there, regardless of whether the value is null. When I type:

="Date: "+ Format([Date],"Short Date")
I get #Error when there is a value, but it does hide correctly when it is null.

="Date: "& Format([Date],"Short Date")
the "date:" never gets hidden if the value is null, but the value shows up correctly when there is a value to display. I've noticed it's only a problem with non-text fields. Any ideas?

this thread has been very helpful so far, Thanks!

 

[maxhugen]

Try:

=iif(isnull([Date]),"","Date: " & Format([Date],"Short Date"))

But I hope you don't really have a field named 'Date', which is a reserved word.

Max Hugen
Australia

 

[Prometheus123]

Thanks a bunch, Max!

That did the trick! And yeah, I was only signifying that it was a date data type.

Cheers,
Bill

 

[maxhugen]

FWIW....
Duane's tip of using '+' works with text fields because trying to add two strings together concatenates them. If the field is Null, you get a 'null propagation'. But with Date and Numeric fields, it does try to add them.

The ampersand '&' will concatenate values as though they were strings, so '3 & 5' will resolve to 35.

Max Hugen
Australia