asked on

Zeros not displaying in report

I have the following in the control source of a report field where TotalNumber is a long integer field in the underlying query/table.
This works great to display either a number or N/A if the field is null but when the number is actually a zero, which I want to display, there is just a blank space.
Any suggestions?

=IIf(IsNull([TotalNumber]),"N/A",[TotalNumber])

chaau

What format have you selected for the filed?
What version of Access you are using?

BTW, I have created a report in Access 2010 and it is displaying OK.

clarkscott

Is this field the result of a left or right join? If so, a null is not the default for a nonexistant value if the related source has no record.

Scott C

esbyrt

ASKER

The field is formatted with #,### with 0 decimal places. It is not a currency field, just a number field that needs the comma since the number can be into the millions and hard to read without that. I am using Access 2010. The field in the table has no format specified, just long integer.

clarkscott, the field is not joined directly but the table it's in is part of a right join. But if I understand you correctly, you are saying the null wouldn't work. The N/A displays properly if the field is empty, it's just a 0 value that won't display.

Thanks for having a look. Any other suggestions?

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

clarkscott

In the join query, instead of simply including the value (that might not exist, add an alias and IIf condition:

MyNewValue: iif(isnull([MyValue], 0, [MyValue])

This will ensure that there is at least a zero in the column.

Scott C

clarkscott

correction: (missed a ")" )
MyNewValue: iif(isnull([MyValue]), 0, [MyValue])

Scott C

esbyrt

ASKER

#,##0 did the trick. Thanks Chaau. (Sorry for the slow response, long weekend away from home and computer).