Solved

Display Text Value If Null Value

Posted on 2008-10-21
8
2,785 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
MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 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