Use of Nz Function in Queries

G

Guest

I’m constructing a Line Tendency Graphic in a Database of Access 2000, from a
Crosstab Query with Columns showing Months of a year and File showing Costs
($) of the desired months with “between Initial and Final Date†criteria
(Chort format). The problem is, the Line Tendency of the graphic doesn’t
shows the months with no records due to these desires months there weren’t
records to register; but I need the Tendency Line shows those as “0â€. I know
the Nz Function solve my problem but I don’t know how and where to insert it
and what other conditions or controls properties do my database must has in
order to get the response that I need.
Guayo, requiring help from Osorno, Chile.
 
J

John Spencer (MVP)

Open the query in design view
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
G

Guest

Ken:
This is the query I'm trying to use with Nz Function for construct the Line
Tendence Graphic. Obviously, the CTQ doesn't show "0" when the desire month
doesn't has records.
TRANSFORM Sum(Nz([SumaDeTotalDaño],0)) AS Expr2
SELECT [SC Vista por CR para Página].NombreCompañía, [SC Vista por CR para
Página].[Nombre de Sucursal], [SC Vista por CR para Página].Centro,
Sum(Nz([SumaDeTotalDaño],0)) AS [Total de SumaDeTotalDaño]
FROM [SC Vista por CR para Página]
WHERE ((([SC Vista por CR para Página].NombreCompañía)=[Empresa]) AND (([SC
Vista por CR para Página].[Nombre de Sucursal])=[Sucursal]) AND (([SC Vista
por CR para Página].Centro)=[Centro de Riesgo]) AND (([SC Vista por CR para
Página].[Fecha deI Incidente]) Between [Fecha inicial] And [Fecha final]))
GROUP BY [SC Vista por CR para Página].NombreCompañía, [SC Vista por CR para
Página].[Nombre de Sucursal], [SC Vista por CR para Página].Centro
PIVOT Format([Fecha deI Incidente],"mmm") In
("Ene","Feb","Mar","Abr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dic");
 
J

John Spencer (MVP)

Not sure without testing, but Probably all you need to do is change the first
line a little bit, so it reads.

TRANSFORM NZ(Sum([SumaDeTotalDaño]),0) AS Expr2

Note that NZ and Sum have switched places
Ken:
This is the query I'm trying to use with Nz Function for construct the Line
Tendence Graphic. Obviously, the CTQ doesn't show "0" when the desire month
doesn't has records.
TRANSFORM Sum(Nz([SumaDeTotalDaño],0)) AS Expr2
SELECT [SC Vista por CR para Página].NombreCompañÖa, [SC Vista por CR para
Página].[Nombre de Sucursal], [SC Vista por CR para Página].Centro,
Sum(Nz([SumaDeTotalDaño],0)) AS [Total de SumaDeTotalDaño]
FROM [SC Vista por CR para Página]
WHERE ((([SC Vista por CR para Página].NombreCompañÖa)=[Empresa]) AND (([SC
Vista por CR para Página].[Nombre de Sucursal])=[Sucursal]) AND (([SC Vista
por CR para Página].Centro)=[Centro de Riesgo]) AND (([SC Vista por CR para
Página].[Fecha deI Incidente]) Between [Fecha inicial] And [Fecha final]))
GROUP BY [SC Vista por CR para Página].NombreCompañÖa, [SC Vista por CR para
Página].[Nombre de Sucursal], [SC Vista por CR para Página].Centro
PIVOT Format([Fecha deI Incidente],"mmm") In
("Ene","Feb","Mar","Abr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dic");

Ken Snell said:
Post the SQL statement of the query that you're using....
Click to expand...
Click to expand...
 
G

Guest

Thanks Ken; I'll try again.
Guayo, from Chile.

John Spencer (MVP) said:
Not sure without testing, but Probably all you need to do is change the first
line a little bit, so it reads.

TRANSFORM NZ(Sum([SumaDeTotalDaño]),0) AS Expr2

Note that NZ and Sum have switched places
Ken:
This is the query I'm trying to use with Nz Function for construct the Line
Tendence Graphic. Obviously, the CTQ doesn't show "0" when the desire month
doesn't has records.
TRANSFORM Sum(Nz([SumaDeTotalDaño],0)) AS Expr2
SELECT [SC Vista por CR para Página].NombreCompañÖa, [SC Vista por CR para
Página].[Nombre de Sucursal], [SC Vista por CR para Página].Centro,
Sum(Nz([SumaDeTotalDaño],0)) AS [Total de SumaDeTotalDaño]
FROM [SC Vista por CR para Página]
WHERE ((([SC Vista por CR para Página].NombreCompañÖa)=[Empresa]) AND (([SC
Vista por CR para Página].[Nombre de Sucursal])=[Sucursal]) AND (([SC Vista
por CR para Página].Centro)=[Centro de Riesgo]) AND (([SC Vista por CR para
Página].[Fecha deI Incidente]) Between [Fecha inicial] And [Fecha final]))
GROUP BY [SC Vista por CR para Página].NombreCompañÖa, [SC Vista por CR para
Página].[Nombre de Sucursal], [SC Vista por CR para Página].Centro
PIVOT Format([Fecha deI Incidente],"mmm") In
("Ene","Feb","Mar","Abr","May","Jun","Jul","Ago","Sep","Oct","Nov","Dic");

Ken Snell said:
Post the SQL statement of the query that you're using....

--

Ken Snell
<MS ACCESS MVP>

I'm constructing a Line Tendency Graphic in a Database of Access 2000,
from a
Crosstab Query with Columns showing Months of a year and File showing
Costs
($) of the desired months with "between Initial and Final Date" criteria
(Chort format). The problem is, the Line Tendency of the graphic doesn't
shows the months with no records due to these desires months there weren't
records to register; but I need the Tendency Line shows those as "0". I
know
the Nz Function solve my problem but I don't know how and where to insert
it
and what other conditions or controls properties do my database must has
in
order to get the response that I need.
Guayo, requiring help from Osorno, Chile.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Use of Nz Function in Access 2000 2
Use of Nz Function in Access 2000 Crosstab Query 2
How and where to insert Nz Function in a Crosstab Query for graphi 1
Use of Nz Function in expresion 1
Use of Nz Function in a Crosstab Query 1
Using the Nz function in a query 1
Need to hide zeros in unused fields. 2
Calculated Controls Nz IIf 7

Top