Crosstab Query Null To Zero Headache

[carlosatcfs]

Hi, have been working on this one for 2 days and getting nowhere, have searched forums, books etc but still no joy.

I have the crosstab query below in Access 2000 which returns some Null values. For graphical representation these need to be zeros. I have tried various incarnations of the Nz function using the Nz(datasource,0) format, for example:

"TRANSFORM Nz(Count(IIf([Number] Is Null,0,[Number])),0) AS [Incident Count]"

but I always get an error message saying:

"Wrong number of arguments used with function in query expression"

I also found a NullToZero function on the web which I imported into my DB, but it always returns network connectivity errors

I've attached a screendump of the design view of the query and the resulting table. Please help.

TRANSFORM Count(IIf([Number] Is Null,0,[Number])) AS [Incident Count]
SELECT QryNewData2.[Pass/Fail]
FROM (QryNewData2 INNER JOIN QryDatesWithYearPeriod ON QryNewData2.RespTimeShort = QryDatesWithYearPeriod.Date) INNER JOIN QryPeriodHistoryMasterByRow2 ON QryDatesWithYearPeriod.YearPeriod = QryPeriodHistoryMasterByRow2.YearPeriod
WHERE (((QryNewData2.[Pass/Fail])="Pass" Or (QryNewData2.[Pass/Fail])="Fail"))
GROUP BY QryNewData2.[Pass/Fail]
ORDER BY QryDatesWithYearPeriod.YearPeriod DESC
PIVOT QryDatesWithYearPeriod.YearPeriod;

 

[boblarson]

The NZ needs to go right next to the number BEFORE any other operations -

NZ([Number],0)

 

[carlosatcfs]

Thanks for the reply. Tried that, same error message:

"Wrong number of arguments used with function in query expression"

SQL below, Access put in the extra () around [Number], not my doing.

TRANSFORM Count(Nz([Number],0)) AS [Incident Count]
SELECT QryNewData2.[Pass/Fail]
FROM (QryNewData2 INNER JOIN QryDatesWithYearPeriod ON QryNewData2.RespTimeShort = QryDatesWithYearPeriod.Date) INNER JOIN QryPeriodHistoryMasterByRow2 ON QryDatesWithYearPeriod.YearPeriod = QryPeriodHistoryMasterByRow2.YearPeriod
WHERE (((QryNewData2.[Pass/Fail])="Pass" Or (QryNewData2.[Pass/Fail])="Fail"))
GROUP BY QryNewData2.[Pass/Fail]
ORDER BY QryDatesWithYearPeriod.YearPeriod DESC
PIVOT QryDatesWithYearPeriod.YearPeriod;

 

[Jon K]

In a crosstab query, you can use Nz(...)+0 in the Transform expression like the following:-

TRANSFORM Nz(Count([Number]))+0 AS [Incident Count]
.

 

[boblarson]

I would suggest not using Access Reserved words for field names (Number is an Access reserved word). Also, no special characters (like / ) or spaces. It will make your life much easier. You can always format the final output the way you want, but using those things can make Access puke.