Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 255
  • Last Modified:

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])
0
esbyrt
Asked:
esbyrt
  • 3
  • 2
  • 2
1 Solution
 
chaauCommented:
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.

Computed filedSample ReportFormat of the field
0
 
clarkscottCommented:
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
0
 
esbyrtAuthor Commented:
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?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
chaauCommented:
As I suggested, it is the format. Change it to "#,##0" and it will work. Read about Format here to better understand the meaning of #

(in fact, you could achieve everything with the format without Iif, using the conditional formatting). Read the article at the above hyperlink, it is very informative
0
 
clarkscottCommented:
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
0
 
clarkscottCommented:
correction:   (missed a ")"   )
MyNewValue: iif(isnull([MyValue]), 0, [MyValue])

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

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now