Text for Nulls in Crosstab

[Guest]

I am doing something wrong and I can't figure it out.

I have a crosstab that I am trying to set Null values to "Missing Value".
Right now, the crosstab creates a seperate column and only shows "Missing
Value" if all columns for my data are null. I need it to appear in each
instance that a case is missing. Please help!

TRANSFORM
Max(IIf(nz([qry_sum_metrics_by_specific_dates].[value],"")="","Missing
Value",[qry_sum_metrics_by_specific_dates].[value])) AS [Value]
SELECT Metrics.metric_id, Summary_Metrics.summary_metric_name,
Vendors.Vendor, Sources.Source
FROM (Vendors RIGHT JOIN (Summary_Metrics RIGHT JOIN (Sources RIGHT JOIN
(Metrics LEFT JOIN Volumes ON Metrics.metric_id = Volumes.Metric_Id) ON
Sources.Source_ID = Metrics.Source_ID) ON Summary_Metrics.summary_metric_id =
Metrics.summary_id) ON Vendors.Vendor_ID = Metrics.Vendor_ID) LEFT JOIN
qry_sum_metrics_by_specific_dates ON Metrics.metric_id =
qry_sum_metrics_by_specific_dates.Metric_Id
WHERE (((Volumes.Vol_Date)>#4/1/2007#))
GROUP BY Metrics.metric_id, Summary_Metrics.summary_metric_name,
Vendors.Vendor, Sources.Source
PIVOT qry_sum_metrics_by_specific_dates.Vol_Date;

 

[John Spencer]

Perhaps what you want is

TRANSFORM NZ(Max([qry_sum_metrics_by_specific_dates].[value]),"Missing
Value") AS [Value]

SELECT Metrics.metric_id, Summary_Metrics.summary_metric_name,
Vendors.Vendor, Sources.Source
FROM (Vendors RIGHT JOIN (Summary_Metrics RIGHT JOIN (Sources RIGHT JOIN
(Metrics LEFT JOIN Volumes ON Metrics.metric_id = Volumes.Metric_Id) ON
Sources.Source_ID = Metrics.Source_ID) ON Summary_Metrics.summary_metric_id
=
Metrics.summary_id) ON Vendors.Vendor_ID = Metrics.Vendor_ID) LEFT JOIN
qry_sum_metrics_by_specific_dates ON Metrics.metric_id =
qry_sum_metrics_by_specific_dates.Metric_Id
WHERE (((Volumes.Vol_Date)>#4/1/2007#))
GROUP BY Metrics.metric_id, Summary_Metrics.summary_metric_name,
Vendors.Vendor, Sources.Source
PIVOT qry_sum_metrics_by_specific_dates.Vol_Date;

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..