Getting rid of Zeros in the Columns (1 Viewer)

[marrett]

I have written a report and I do not want the zeros to show up. I tried using the IIF Statement but I kept getting errors. My statement: IIf([Sponsor]=0,"",[Sponsor])
I just get error. Sponsor is a dollar amount.

Thanks

 

[Rich@ITTC]

Hi Marrett

Have you made sure that you text box is not called Sponsor - if your text box is named with the same name as your field from the table this will cause a conflict? Is the text box set to a particular format (e.g Currency?) - perhaps a "" value is not acceptable to the format setting (as a null monetary value would normally equate to 0 dollars). Also another possible problem is the field/text box having Required property set to Yes. The only other suggestion I have is to bracket the "condition" part of you expression.

IIf(([Sponsor]=0),"",[Sponsor])

Making a very simple database I managed to create what you wanted to achieve, using similar to above.

 

[marrett]

Thanks for your response, but I still get error. I tried: =IIf(([Sponsor]=0),"",[Sponsor])and =IIf(([Sponsor]=0),null,[Sponsor])and I still get error.

The simplest things can be such a pain.

Thanks,

maria



[This message has been edited by marrett (edited 10-18-2000).]

 

[Pat Hartman]

I'm going to expand on what Rich said.

When you use the wizard to build a report, Access assigns the field name from the recordsource as the control name. This usually does not cause a problem. However, if you do a calculation in the control source, the control name (on the Other tab of the properties dialog) must be different from the name of any field in the record source. For consistancy, after I let the wizard build a form for me, I go into the name field of each control and change its value by adding a prefix of "txt". Try changing the control name of the Sponsor field to txtSponsor.