Want '0's to print instead of blanks in textboxes

[laina222]

Hi everyone!
I have a report that displays textboxes with numerical totals. When the total for that textbox is '0', nothing prints. How do I get the textbox to print '0' instead of a blank?

 

[67peshawar294]

Hi,
Try;
=IIf(IsNull([yourfield]),0,[yourfield])
that should do it.
If you are adding fields you can concantate like;
=IIf(IsNull([yourfield]),0,[yourfield])+IIf(IsNull([2ndfield]),0,[2ndfield])+IIf(IsNull([3rdfield]),0,[3rdfield])

 

[laina222]

Should this be in the Control Source for the textbox? If not, where?

 

[67peshawar294]

Yes, put it in the control source.
Jim

 

[JoeMiller]

You can also use the format of the textbox control to dictate this. In a number field you can specify 4 different formats depending on the value separated by semi-colons.

PositiveNbrs;NegativeNbrs;ZeroNbrs;NullNbrs

Here's a sample format that should work for you.

0;-0;0;0

Suppose you wanted to show "N/A" for null numbers, it would look like this:

0;-0;0;"N/A"

HTH Joe Miller
joe.miller@flotech.net

 

[67peshawar294]

Joe and Ami,
When I tried this it gave me 0 values in my result.
I have a concantated field and the entries with no value gave me 0 for the total even though there were other values in the row.
Using the IIF the values add correctly.
Jim

 

[JoeMiller]

When you concatenate a field you turn the result into text so it is no longer a number field. You can turn the result back into a number (so that my formatting can work) by using the val statement around your concatenation function:

Val(ConcatField1&ConcatField2)

HTH Joe Miller
joe.miller@flotech.net