?
Solved

Display Text Value If Null Value

Posted on 2008-10-21
8
Medium Priority
?
2,793 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

801 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