Solved

Zeros not displaying in report

Posted on 2013-05-14
7
246 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

Accepted Solution

by:
chaau earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
correction:   (missed a ")"   )
MyNewValue: iif(isnull([MyValue]), 0, [MyValue])

Scott C
0
 

Author Closing Comment

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

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
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…

728 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now