show 0 in crosstabquery

  • Thread starter Thread starter hermie
  • Start date Start date
H

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
 
hermie said:
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?
Click to expand...

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

Rick Brandt said:
hermie said:
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?
Click to expand...

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.
Click to expand...
 
Thanks Rick
works now as wanted

Herman
Rick Brandt said:
hermie said:
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?
Click to expand...

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.
Click to expand...
 
Back
Top