Please show zeros in my report!!!

[WB786]

I can't seem to find andanswer to this. I know it has to be simple one.

I need to show a zero for any data field that has no value stored on the table.

I tried using IIf(IsNull[MyField],0)) or variation of this but with no luck.

Anyone can help???

Thanks,
WB

WB

 

[jrbarnett]

Nz(MyField,0)

Remember though that the difference between 0 and Null can cause differences in totals within forms, reports etc.

John

 

[dhookom]

In addition to John's comments, you can set the Format property of a text box to display a zero if the value is null. This would not have an effect on averages of the values.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[WB786]

Duane,
How do you set the format of the field if the current format is say set to Currency or Number?

Thanks,
WB

WB

 

[MinusM]

I don't understand why John's method is not working. Try setting the control source as =Nz([MyField],0)
This does not work in rare cases when the return value is a calculated fied which is Empty
While using Format,
You cannot use the Equivalent name of Currency or Number. use the actual format "$#,###.##"
if you check the help of Format, you will know it has 4 optional parameters for positive numbers, negative numbers, 0 and null respectively

So the Format String should be something like
$#,###.##;;$#,###.##;$#,###.##

Best of luck

 

[WB786]

Thank you all for the insight. I will try that. Will let you know if I need further help.

WB

WB