Hiding null records in report with Iif (1 Viewer)

[VABlitz]

I have a report based on a query with 3 fields (Tested, RMA, OpenRMA)
The query searches all the tables in my database and gives me a count of the equipment tested, RMA reported and open rma.
The report has two fields (Equipment, NumberTested) with a total below and also a total of the RMAs and OpenRMAs. I'm trying to hide RMAs and OpenRMAs from appearing in the body and only showing below the total. All three totals are showing correctly, but I am still seeing RMA and Open RMA with a null in the count in the body

So in the report's Equipment.control I wanted to put
Iif([NumberTested] is null, null, [Equipment])

Meaning if the numbertested field is null the equipment field will be blank, if it is not just leave what is in the equipment field there, but it returns a #Error in the printout. It seems to handle the two null records correctly. Trying Equipment.Value also returned the same error.

 

[CJ_London]

you could use conditional formatting to make the forecolor the same as the backcolor which will effectively 'hide' it

 

[VABlitz]

Per your suggestion I tried this:
Iif([NumberTested] is null, [Equipment].Forecolor = 16777215, [Equipment].ForeColor = 0)
but instead of the data that is in [Equipment] changing color, I received a -1 or 0. Did I enter somethine wrong?

 

[VABlitz]

Nevermind found a different solution. Changed the Filter to read:
Equipment <> "Open RMA" And Equipment <> "RMA"
and turned the Filter On to Yes

 

[jdraw]

Try
Iif(IsNull([NumberTested]) , [Equipment].Forecolor = 16777215, [Equipment].ForeColor = 0)

 

[VABlitz]

On second thought my solution fixed it in the body, but my totals for RMA open and closed are gone from the total section.

 

[VABlitz]

Thanks for the help. I thought of a workaround to the problem. I changed my SQL Query to not label those two fields. While this creates a blank spot to appear above my Totals, it is the easiest option. If only I would have thought of this 24 hours ago