need conditional output of a column based on contents 1

[msc0tt]

I've got a column (text box) 'bid' that prints out a currency amount. If the amount is $0.00, I wish the output to be blank. I changed the Control Source from 'bid' to =iif([bid] = 0, "", [bid]) but only get errors. My 3rd party references don't cover this technique.

-with thanks, Mike Scott

 

[CosmoKramer]

Mike,

You can't use functions like IIf in a bound text box. Try adding a new text box and set your IIf statement as its control source. That should work.....

 

[msc0tt]

Thanks Cosmo.
Yes, I just found this solution in an earlier post "Troubleshoot IIf statement". It only works for one of two cases I have. The other case is a little more complicated. The field is extracted with:
SELECT [guest].[sortname] & "(" [guest].

& &quot" AS sortname FROM ...

My report uses the Control Source as [sortname]. If the record is blank, I get '()' on my report. I tried:
=iif([sortname] = "()", "", [sortname])

I still get the empty parenthesis on blank records.
Any ideas? -thanks

 

[CosmoKramer]

See if this makes sense...

Leave your [sortname] field alone. Add an unbound text box that should have something like this as its control source:

=IIf([sortname] Is Null,"",[sortname] & "(" & [table] & ")")

This should get rid of the extra parens, only the records with a sortname will have them.....

 

[msc0tt]

Change not necessary -my mistake. The dreaded "invisible space" strikes again. My select actually returns " ()" on an empty record (a SPACE preceeds parenthesis). Sorry for the wasted keystrokes. (no doubt your suggestion would have worked, and was star-worthy).
Mike