How do you suppress zero amounts in access reports?

Question

_{Monday, June 21, 2010 5:32 AM}

Hi,

I'm on Access via MS Office XP Pro on Windows 7 Business.

I want to supress zero value fields on my report.  I've read the following answers on the web:

"The Format property of the text box allows this. You can set 4 separate formats for different types of values: positive numbers, negative numbers, zeros, and Null."

When I look in the Format Property of the text box on my report, I have the following options:

Currency
Euro
Fixed
Standard
Fixed
Scientific
True/False
Yes/No

I do not have positive numbers, negative numbers, zeros, and Null. 

I currently use  "=IIf([DepositAmt]=0,"",[DepositAmt])" in the Field's Control Source.  Is there a bettery way?

Dennis

All replies (4)

_{Monday, June 21, 2010 8:59 PM ✅Answered | 1 vote}

Dennis,

Your solution is fine.

Alternatively, you can use just DepositAmt in the Control Source property and use conditional formatting where you set ForeCollor same as BackgroundColor if the value=0

In report's Design view, click on the text boxt bound to DepositAmt, then on the ribbon click Conditional and set FieldValueIs-EqualsTo-0, then set color for the font as white (or whatever your background color is).

Using this technique, you can hide from showing on the report any values you want.

Nadia

_{Monday, June 21, 2010 9:02 PM ✅Answered | 3 votes}

The Format property offers a list of options... but you are not limited to that list! You can type any valid FORMAT string into the property.

To have NULL and zero values show as blank, positive as just a number like 3146, negative as -2285, use a Format such as

#;-#;"";""

If the values are dollar figures and you want a leading $ and comma, you can specify that too:

$#,##0.00;($#,##0.00)[Red];"";"You left this value out!"

The VBA Help page gives detailed options (oddly, the regular Help does not).

John W. Vinson/MVP

_{Monday, June 21, 2010 12:33 PM}

Hello,

If you do not want show leading zeroes, so that A="000123" would show "123", then you can use Val function

Val(A) would show "123".  If A="jkjk" (not numbers), then Val(A)=0

Nadia

_{Monday, June 21, 2010 4:24 PM}

Nadia,

Oops, I realized my title question is wrong.  However, I like your answer for another situation I have.

What I want to do is suppress the zero value field from printing.  If the value is zero, I want to print blank.

Thanks,

Dennis