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(ctbStatusBySection.[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(qryAllTPRsWithoutReplacedPartsData.prob_stat us_text) AS [Count]
SELECT qryAllTPRsWithoutReplacedPartsData.SectionName, Count(qryAllTPRsWithoutReplacedPartsData.prob_stat us) AS Total
FROM qryAllTPRsWithoutReplacedPartsData
GROUP BY qryAllTPRsWithoutReplacedPartsData.SectionName
ORDER BY qryAllTPRsWithoutReplacedPartsData.SectionName
PIVOT qryAllTPRsWithoutReplacedPartsData.prob_status_tex t;

Here is the Row Source from the chart:

SELECT ctbStatusBySection.SectionName, Sum(ctbStatusBySection.Open) AS Open, Sum(ctbStatusBySection.[Ready for Closure]) AS [Ready for Closure], Sum(ctbStatusBySection.Closed) AS Closed FROM ctbStatusBySection GROUP BY ctbStatusBySection.SectionName ORDER BY Sum(ctbStatusBySection.Open) DESC , Sum(ctbStatusBySection.[Ready for Closure]) DESC , Sum(ctbStatusBySection.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(qryAllTPRsWithoutReplacedPartsData.Sect ionName),"No Section Name",qryAllTPRsWithoutReplacedPartsData.SectionNa me)

[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.

tblProblemStatus
status_id status_text
1 Open
2 Closed
5 Ready for Closure

Crosstab Query:
TRANSFORM Count(qryAllTPRsWithoutReplacedPartsData.prob_stat us_text) AS [Count]
SELECT qryAllTPRsWithoutReplacedPartsData.SectionName, Count(qryAllTPRsWithoutReplacedPartsData.prob_stat us) AS Total
FROM tblProblemStatus LEFT JOIN qryAllTPRsWithoutReplacedPartsData ON tblProblemStatus.status_id = qryAllTPRsWithoutReplacedPartsData.prob_status
GROUP BY qryAllTPRsWithoutReplacedPartsData.SectionName
ORDER BY qryAllTPRsWithoutReplacedPartsData.SectionName
PIVOT tblProblemStatus.status_text;

[Denburt]

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