Making Text Labels Not Show Up If Corresponding Vale Is Null/0. 2

[MSWhat]

Hi, all.

I'm wondering if it is possible to set some code/formatting to make it such that the text lable does not show up on a report if its corresponding value is either null or zero. For example, if my report looked like this:

Number of red popsicles purchased: 17
Number of blue popsicles purchased: 0

Is there a way to make "Number of blue popsicles purchased" not show up since it's value is 0. Right now, I've made it so that there is a conditional formating set for the form that If the value for popsicles purchased is exactly zero then the text is white (so you don't see the zero at all).

Thanks in advance for your help.

 

[Remou]

You can put the label in a textbox, along with the result:

[tt]=IIF([Popsicles]>0,"Number of " & [Colour] & " popsicles purchased: " & [Popsicles],"")[/tt]

 

[dhookom]

I would suggest the following which would shrink if needed:
=IIF([Popsicles]>0,"Number of " & [Colour] & " popsicles purchased: " & [Popsicles],Null)

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]

 

[MSWhat]

Thanks so much, Remou. I tried inserting the code, adapting it to my actual controls, but can't seem to get it. Here's the code:

=IIF([NMTCs]>0, "NMTCs:" " & [NMTCs],")

In other words, if the number of NMTCs is greater then zero, then I want it to display "NMTCs" and the NMTC value for each record in my report.

Thanks, thanks, thanks.

 

[Remou]

Change the quotes:

=IIF([NMTCs]>0, "NMTCs: " & [NMTCs],"")

 

[MSWhat]

When I enter it, the code turns red, sometimes giving a compile eror. I think some of the problem may be in the fact that I may have entered it into the wrong place? I selected the control in my report and then just clicked the "..." builder. Is that correct?

With DHookom's advice, this is the new code:

Option Compare Database
=IIF([NMTCs]>0," NMTCs: " & [NMTCs],Null)
End Sub

Thanks to all.

 

[Remou]

Or as DHookom says,

=IIF([NMTCs]>0, "NMTCs: " & [NMTCs],Null)

 

[MSWhat]

Remou, just tried your suggestion wiht the new code:

=IIF([NMTCs]>0, "NMTCs: " & [NMTCs],"")

and it's still red.

 

[Remou]

Did you change the name of the control to something that does not match the name of a field?

 

[MSWhat]

Nope. Just copy/pasted the name of the control, "NMTCs

 

[Remou]

I mean, please rename the control to something etc.

 

[MSWhat]

Changed the text box to "NMTCs Text Box". So now the code reads:

Option Compare Database
=IIF([NMTCs Text Box]>0, "NMTCs: " & [NMTCs Text Box],Null)
End Sub

I also tried:

Option Compare Database
=IIF([NMTCs]>0, "NMTCs: " & [NMTCs Text Box],Null)
End Sub

"NMTCs" is the name of the column in the table/query from which this form is based. "NMTCs Text Box" is the name of the textbox on the form. Did I do that properly?

Thanks, Remou.

 

[Remou]

The general idea is to set the control source of a textbox to the line. The textbox should have a name other than the name of a field. You need to create a textbox on your report in design view and cut and paste the line below into it:

=IIF([NMTCs]>0, "NMTCs: " & [NMTCs],Null)

 

[MSWhat]

OOOOO, I didn't realize this was supposed to be the control source! I thought this was code, built in association with the text box, which had a control source of "NMTCs".

Brilliant. Thanks so much for sticking through all of this with me. Sorry for my ignorance.

 

[MSWhat]

My final quesiton:

I've done that, however my formatting of the number as a currency with no decimal points is lost. That is, whereas before it would have showed "$1,176,483", it now shows "NMTCs: 1176483".

Any way to format it to show as a currency?

This happened in another column when I wanted to add parentheses around a number to indicate that it was negative, it also lost its formatting as a currency value.

 

[Remou]

Yes.

=IIF([NMTCs]>0, "NMTCs: " & Format([NMTCs],"Currency"),Null)

 

[MSWhat]

Brilliant!

And for my other which has parentheses around it, I'll do

="(" & [Return of Equity] & ")", "Currency"

Is that correct?

 

[Remou]

You need format:

="(" & Format([Return of Equity],"Currency") & ")

 

[MSWhat]

You're the best!

 

[MSWhat]

I'm now trying to work on a field which I only want to show up if it Is Not Null. So, on my report, if there is a value entered for the AM position, I want my report to display "AM:" and the AM's name.

Here's the code for the textbox that I have, but it isn't working. Any clues?

=IIF([AM] IsNotNull, "AM: " & [AM], Null)