Solved

Display Text Value If Null Value

Posted on 2008-10-21
8
2,779 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

785 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