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.
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 ;
isnull(feild name, "Alternative value")
so for you its
isnull(field ,"No Bids")
if you do it in a query, change the , for a ;
ASKER
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.
=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.
add a as clause
=Nz([FieldName], "No Bids") as FieldName1
i think access use ":"
FieldName1 : Nz([FieldName], "No Bids")
=Nz([FieldName], "No Bids") as FieldName1
i think access use ":"
FieldName1 : Nz([FieldName], "No Bids")
ASKER
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=t
GROUP BY tblEmployee.EmployeeName
PIVOT tblBidStatus.BidStatus In ("In Process","Pending","Awarde
I have tried inserting the NZ function inside both Count functions, but I do not get any different results.
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=t
GROUP BY tblEmployee.EmployeeName
PIVOT tblBidStatus.BidStatus In ("In Process","Pending","Awarde
I have tried inserting the NZ function inside both Count functions, but I do not get any different results.
Try the alternative approach,
new select query: newfield1:nz(fieldfromxtab
Attach the relevant objects in a database if you need to stick to one approach!
new select query: newfield1:nz(fieldfromxtab
Attach the relevant objects in a database if you need to stick to one approach!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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