Solved

Zeros not displaying in report

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

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

856 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