Solved

Display Text Value If Null Value

Posted on 2008-10-21
8
2,775 Views
Last Modified: 2013-11-28
I have a report based on a crosstab query.  The crosstab query performs a count function.  Occasionally, the count will return null if no records exist.  For the values that are null, I would like to show "No Bids" on the report.  I have attempted to use the NZ function as well as different OnFormat events, but am not having any luck.  The NZ function results in an error.
0
Comment
Question by:bayouexpert
  • 4
  • 2
  • 2
8 Comments
 
LVL 30

Expert Comment

by:hnasr
ID: 22770358
on approach:
 Format each relevant field in the report as = nz(field,"NoBid")

Alternate:
  Build the report on a select query based on the xtab query.
   In the select query, add nz(fieldname,"NoBid") to all fields
0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 22770474
there is a isnull() function
isnull(feild name, "Alternative value")
so for you its
isnull(field ,"No Bids")
if you do it in a query, change the , for a ;
0
 

Author Comment

by:bayouexpert
ID: 22770705
I attempted to place the following in the control source for each field in the report...

=Nz([FieldName], "No Bids")

The function works for some of the fields, but others result in an error when the report is run.  The error is an invalid control property error that makes mention of a circular reference.
0
 
LVL 18

Expert Comment

by:David Robitaille
ID: 22770724
add a as clause
=Nz([FieldName], "No Bids") as FieldName1
i think access use ":"
FieldName1 : Nz([FieldName], "No Bids")
 
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 18

Expert Comment

by:David Robitaille
ID: 22770740
0
 

Author Comment

by:bayouexpert
ID: 22770855
davrob60...I believe that I have the same format as in the link you posted.

The query that the report is based on is as follows....

TRANSFORM Count(tblBidLog.BidID) AS CountOfBidID
SELECT tblEmployee.EmployeeName, Count(tblBidLog.BidID) AS [Total Of BidID]
FROM tblEmployee LEFT JOIN (tblBidStatus RIGHT JOIN tblBidLog ON tblBidStatus.BidStatusID=tblBidLog.BidStatusID) ON tblEmployee.EmployeeID=tblBidLog.EmployeeID
GROUP BY tblEmployee.EmployeeName
PIVOT tblBidStatus.BidStatus In ("In Process","Pending","Awarded","Rejected","Canceled");

I have tried inserting the NZ function inside both Count functions, but I do not get any different results.
0
 
LVL 30

Expert Comment

by:hnasr
ID: 22771015
Try the alternative approach,
new select query: newfield1:nz(fieldfromxtabquery,"NoBid") and so on.

Attach the relevant objects in a database if you need to stick to one approach!
0
 
LVL 18

Accepted Solution

by:
David Robitaille earned 125 total points
ID: 22771037
TRANSFORM nz(Count(tblBidLog.BidID),0)  AS CountOfBidID
should work

  http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23786289.html
0

Featured Post

What Security Threats Are You Missing?

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

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

21 Experts available now in Live!

Get 1:1 Help Now