Zeros not displaying in report

Posted on 2013-05-14
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?

Question by:esbyrt
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
LVL 25

Expert Comment

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
LVL 20

Expert Comment

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

Author Comment

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?
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 25

Accepted Solution

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
LVL 20

Expert Comment

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
LVL 20

Expert Comment

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

Scott C

Author Closing Comment

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

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

630 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