• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2798
  • Last Modified:

Display Text Value If Null Value

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
bayouexpert
Asked:
bayouexpert
  • 4
  • 2
  • 2
1 Solution
 
hnasrCommented:
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
 
David RobitailleAnalyst ProgrammerCommented:
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
 
bayouexpertAuthor Commented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
David RobitailleAnalyst ProgrammerCommented:
add a as clause
=Nz([FieldName], "No Bids") as FieldName1
i think access use ":"
FieldName1 : Nz([FieldName], "No Bids")
 
0
 
David RobitailleAnalyst ProgrammerCommented:
0
 
bayouexpertAuthor Commented:
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
 
hnasrCommented:
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
 
David RobitailleAnalyst ProgrammerCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now