show 0 in crosstabquery

[hermie]

Hello

In my crosstabquery i have some field which shows blanks. I want to see a 0.
How can I manage to show a 0 insteas of a blank?
I tried to NZ function with no result.
Example:
city total fieldA FieldB
a 5 1 4
b 7 7 0

Here is the SQL
TRANSFORM Count([HG-001 query].SEG_SOC_PART) AS CountOfSEG_SOC_PART
SELECT [HG-001 query].[MUNICIPIO-REFERIDO], Count([HG-001
query].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [HG-001 query]
GROUP BY [HG-001 query].[MUNICIPIO-REFERIDO], [HG-001 query].Inactivo
PIVOT [HG-001 query].Prog_plura;

How can i show the 0 value?

Herman

 

[Rick Brandt]

Hello

In my crosstabquery i have some field which shows blanks. I want to
see a 0. How can I manage to show a 0 insteas of a blank?
I tried to NZ function with no result.
Example:
city total fieldA FieldB
a 5 1 4
b 7 7 0

Here is the SQL
TRANSFORM Count([HG-001 query].SEG_SOC_PART) AS CountOfSEG_SOC_PART
SELECT [HG-001 query].[MUNICIPIO-REFERIDO], Count([HG-001
query].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [HG-001 query]
GROUP BY [HG-001 query].[MUNICIPIO-REFERIDO], [HG-001 query].Inactivo
PIVOT [HG-001 query].Prog_plura;

How can i show the 0 value?

Where did you use Nz()? That has always worked for me. Change the Count in
the query designer grid to Expression, move the Count() up into the field
defintion and wrap Nz() around it.

 

[Duane Hookom]

To expand on Rick's excellent advice, here is the expression I would use:

TRANSFORM Val(Nz(Count([HG-001 query].SEG_SOC_PART),0)) AS
CountOfSEG_SOC_PART
--
Duane Hookom
MS Access MVP
--

Hello

In my crosstabquery i have some field which shows blanks. I want to
see a 0. How can I manage to show a 0 insteas of a blank?
I tried to NZ function with no result.
Example:
city total fieldA FieldB
a 5 1 4
b 7 7 0

Here is the SQL
TRANSFORM Count([HG-001 query].SEG_SOC_PART) AS CountOfSEG_SOC_PART
SELECT [HG-001 query].[MUNICIPIO-REFERIDO], Count([HG-001
query].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [HG-001 query]
GROUP BY [HG-001 query].[MUNICIPIO-REFERIDO], [HG-001 query].Inactivo
PIVOT [HG-001 query].Prog_plura;

How can i show the 0 value?

Where did you use Nz()? That has always worked for me. Change the Count
in
the query designer grid to Expression, move the Count() up into the field
defintion and wrap Nz() around it.

 

[hermie]

Thanks Rick
works now as wanted

Herman

Hello

In my crosstabquery i have some field which shows blanks. I want to
see a 0. How can I manage to show a 0 insteas of a blank?
I tried to NZ function with no result.
Example:
city total fieldA FieldB
a 5 1 4
b 7 7 0

Here is the SQL
TRANSFORM Count([HG-001 query].SEG_SOC_PART) AS CountOfSEG_SOC_PART
SELECT [HG-001 query].[MUNICIPIO-REFERIDO], Count([HG-001
query].SEG_SOC_PART) AS [Total Of SEG_SOC_PART]
FROM [HG-001 query]
GROUP BY [HG-001 query].[MUNICIPIO-REFERIDO], [HG-001 query].Inactivo
PIVOT [HG-001 query].Prog_plura;

How can i show the 0 value?

Where did you use Nz()? That has always worked for me. Change the Count in
the query designer grid to Expression, move the Count() up into the field
defintion and wrap Nz() around it.