Crosstab chart does not recognize field name as valid.

[kc5]

I have a chart based on a crosstab query of SectionName versus Status where Status is Open, Ready for Closure, or Closed. The problem is that when there are no SectionNames at status Ready for Closure, I get errors and my chart is blank. I understand the problem but I'm not a programmer and don't know how to fix it. It seems like an IIF statement around the Row Source SELECT phrase "Sum(ctbStatusB ySection.[Ready for Closure]) AS [Ready for Closure]" would fix it, but I don't know the correct syntax.

Here is the SQL Code from the crosstab query:

TRANSFORM Count(qryAllTPR sWithoutReplace dPartsData.prob _status_text) AS [Count]
SELECT qryAllTPRsWitho utReplacedParts Data.SectionNam e, Count(qryAllTPR sWithoutReplace dPartsData.prob _status) AS Total
FROM qryAllTPRsWitho utReplacedParts Data
GROUP BY qryAllTPRsWitho utReplacedParts Data.SectionNam e
ORDER BY qryAllTPRsWitho utReplacedParts Data.SectionNam e
PIVOT qryAllTPRsWitho utReplacedParts Data.prob_statu s_text;

Here is the Row Source from the chart:

SELECT ctbStatusBySect ion.SectionName , Sum(ctbStatusBy Section.Open) AS Open, Sum(ctbStatusBy Section.[Ready for Closure]) AS [Ready for Closure], Sum(ctbStatusBy Section.Closed) AS Closed FROM ctbStatusBySect ion GROUP BY ctbStatusBySect ion.SectionName ORDER BY Sum(ctbStatusBy Section.Open) DESC , Sum(ctbStatusBy Section.[Ready for Closure]) DESC , Sum(ctbStatusBy Section.Closed) DESC;

[Denburt]

Typing off the cuff here but try this:

TRANSFORM Count(qryAllTPRsWithoutReplacedPartsData.prob_stat us_text) AS [Count]
SELECT iif(Isnull(qryAllTPRsWithoutReplacedPartsData.SectionName),"No Section Name",qryAllTPRsWithoutReplacedPartsData.SectionName), Count(qryAllTPRsWithoutReplacedPartsData.prob_status) AS Total
FROM qryAllTPRsWithoutReplacedPartsData
GROUP BY iif(Isnull(qryAllTPRsWithoutReplacedPartsData.SectionName),"No Section Name",qryAllTPRsWithoutReplacedPartsData.SectionName) ORDER BY  iif(Isnull(qryAllTPRsWithoutReplacedPartsData.SectionName),"No Section Name",qryAllTPRsWithoutReplacedPartsData.SectionName)
PIVOT qryAllTPRsWithoutReplacedPartsData.prob_status_text;

Essentially checking SectionName every time to make sure something is entered into that field as such.

iif(Isnull(qryA llTPRsWithoutRe placedPartsData .SectionName)," No Section Name",qryAllTPR sWithoutReplace dPartsData.Sect ionName)

[kc5]

Thanks Denburt, but that didn't work.I got help from a cotract programmer in Cincinnati (my son-in-law) and here is what he figured out:

Basically he created a new table of all the status codes and LEFT JOINed on the status code in my table.

tblProblemStatu s
status_id status_text
1 Open
2 Closed
5 Ready for Closure

Crosstab Query:
TRANSFORM Count(qryAllTPR sWithoutReplace dPartsData.prob _status_text) AS [Count]
SELECT qryAllTPRsWitho utReplacedParts Data.SectionNam e, Count(qryAllTPR sWithoutReplace dPartsData.prob _status) AS Total
FROM tblProblemStatu s LEFT JOIN qryAllTPRsWitho utReplacedParts Data ON tblProblemStatu s.status_id = qryAllTPRsWitho utReplacedParts Data.prob_statu s
GROUP BY qryAllTPRsWitho utReplacedParts Data.SectionNam e
ORDER BY qryAllTPRsWitho utReplacedParts Data.SectionNam e
PIVOT tblProblemStatu s.status_text;

[Denburt]

Cool glad you had the resources and he managed to get you rolling. Thanks for letting us know.