Solved

Zeros not displaying in report

Posted on 2013-05-14
7
248 Views
Last Modified: 2013-05-20
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
Comment
Question by:esbyrt
  • 3
  • 2
  • 2
7 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39167203
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
 
LVL 20

Expert Comment

by:clarkscott
ID: 39168159
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
 

Author Comment

by:esbyrt
ID: 39170317
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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
ID: 39170343
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
 
LVL 20

Expert Comment

by:clarkscott
ID: 39174662
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
 
LVL 20

Expert Comment

by:clarkscott
ID: 39174668
correction:   (missed a ")"   )
MyNewValue: iif(isnull([MyValue]), 0, [MyValue])

Scott C
0
 

Author Closing Comment

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

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question