Crosstab Report Help Needed!

[Guest]

I have a report based on a crosstab query. I have specified my column headings, but when there is no data for one of the columns, the calculation on my report does not work. Below is the SQL for the crosstab query and also the calculated fields on the report.

Crosstab Query:
TRANSFORM Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround
SELECT qryTurnAnalysis.ClientAdministrator, Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround1
FROM qryTurnAnalysis
GROUP BY qryTurnAnalysis.ClientAdministrator
PIVOT Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like "[3]","48 hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6]","1 wk",[Turnaround] Like "[7-8-9]","7-9",[Turnaround] Like "10","10",[Turnaround] Like "11","11",True,">2 wks") In ("24 hrs","48 hrs","72 hrs","1 wk","7-9","10","11",">2 wks");

Report Calcuation
=Nz(([7-9])+Nz([10])+Nz([11]))

There is no data for the column field [7-9],but there is data for [10] and [11]. It will not add the columns together.

Any help will be greatly appreciated.

 

[Duane Hookom]

I find it hard to believe the number of times I see Nz() without providing a
second argument.
=Nz([7-9],0)+Nz([10],0)+Nz([11],0)
In addition, your ()s were off a bit.

--
Duane Hookom
MS Access MVP

I have a report based on a crosstab query. I have specified my column

headings, but when there is no data for one of the columns, the calculation
on my report does not work. Below is the SQL for the crosstab query and
also the calculated fields on the report.

Crosstab Query:
TRANSFORM Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround
SELECT qryTurnAnalysis.ClientAdministrator,

Count(qryTurnAnalysis.Turnaround) AS CountOfTurnaround1

FROM qryTurnAnalysis
GROUP BY qryTurnAnalysis.ClientAdministrator
PIVOT Switch([Turnaround] Like "[1-2]","24 hrs",[Turnaround] Like

"[3]","48 hrs",[Turnaround] Like "[4]","72 hrs",[Turnaround] Like "[5-6]","1
wk",[Turnaround] Like "[7-8-9]","7-9",[Turnaround] Like
"10","10",[Turnaround] Like "11","11",True,">2 wks") In ("24 hrs","48
hrs","72 hrs","1 wk","7-9","10","11",">2 wks");

Report Calcuation
=Nz(([7-9])+Nz([10])+Nz([11]))

There is no data for the column field [7-9],but there is data for [10] and

[11]. It will not add the columns together.