Nulls in Chart

[devouru]

I have a chart showing vendor activity monthly. If the a vendor has no
activity in a month, a null is returned and cannot be plotted. How can I
place a 0 for null in the chart? TIA folks

-JS

 

[alan fisher]

us the Nz function. It will return a zero if the value is
null. Look in help for exactly how to use it.

 

[devouru]

Or maybe I should say if the vendor id does not have data in the month, how
would I invoke the NZ function?

-JS

 

[alan fisher]

I'm not sure exactly what you need but try puting some
code in the On Print or On Format of the section of the
report in question that ckecks for no data. Maybe create a
field in the query that Nz's the data you are
calling "activity" and then use it as the data source for
the report. Sorry I couldn't be of more help without
actually seeing what you are doing.

 

[Duane Hookom]

It would help if we could see the SQL of the Row Source for the chart. Your
issue could be resolved with a LEFT or RIGHT JOIN which displays all the
records from one table.

 

[Rick Brandt]

I am familiar with NZ. If the vendor code is the Group By Column Heading,
and the query comes up with no data, how would you invoke the NZ function?
TIA

Assuming that you are plotting data "per month" or similar you need to
create a table that has all months represented and then outer join to that
in your query so that all months are represented even when the other tables
have no data.

A caveat here is that you need to consider the type of data you are
charting before you do this. If you are just charting deliveries or
delivery amounts, then it should be harmless. However; if this were a QA
database and you were charting rejects or late deliveries, then you
wouldn't want months with no activity to be plotted the same as months with
activity, but zero instances. A vendor's performance should not be
bolstered up merely by having months where he has done nothing.

 

[devouru]

It is a monthly scrap cost for vendor. Problem is when there is no scrap
cost in the month for a CCID, it comes up null and I have a hole in my
chart. Thankx ya'll


TRANSFORM Sum([DMRDB SL].ExtCOST) AS SumOfExtCOST SELECT
Format([Date],"mmm"" '""yy") AS Expr1 FROM [DMRDB SL] GROUP BY
(Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy")) PIVOT
[DMRDB SL].CCID;

-JS

 

[Duane Hookom]

Try:
TRANSFORM Val(Nz(Sum([DMRDB SL].ExtCOST),)) AS SumOfExtCOST
SELECT Format([Date],"mmm"" '""yy") AS Expr1
FROM [DMRDB SL]
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy"))
PIVOT [DMRDB SL].CCID;

--
Duane Hookom
MS Access MVP

It is a monthly scrap cost for vendor. Problem is when there is no scrap
cost in the month for a CCID, it comes up null and I have a hole in my
chart. Thankx ya'll


TRANSFORM Sum([DMRDB SL].ExtCOST) AS SumOfExtCOST SELECT
Format([Date],"mmm"" '""yy") AS Expr1 FROM [DMRDB SL] GROUP BY
(Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy")) PIVOT
[DMRDB SL].CCID;

-JS

Assuming that you are plotting data "per month" or similar you need to
create a table that has all months represented and then outer join to that
in your query so that all months are represented even when the other tables
have no data.

A caveat here is that you need to consider the type of data you are
charting before you do this. If you are just charting deliveries or
delivery amounts, then it should be harmless. However; if this were a QA
database and you were charting rejects or late deliveries, then you
wouldn't want months with no activity to be plotted the same as months with
activity, but zero instances. A vendor's performance should not be
bolstered up merely by having months where he has done nothing.

 

[devouru]

That's a winner! I threw NZ at it everywhere. Cool, thankx a bunch.

-JS

Try:
TRANSFORM Val(Nz(Sum([DMRDB SL].ExtCOST),)) AS SumOfExtCOST
SELECT Format([Date],"mmm"" '""yy") AS Expr1
FROM [DMRDB SL]
GROUP BY (Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy"))
PIVOT [DMRDB SL].CCID;

--
Duane Hookom
MS Access MVP

It is a monthly scrap cost for vendor. Problem is when there is no scrap
cost in the month for a CCID, it comes up null and I have a hole in my
chart. Thankx ya'll


TRANSFORM Sum([DMRDB SL].ExtCOST) AS SumOfExtCOST SELECT
Format([Date],"mmm"" '""yy") AS Expr1 FROM [DMRDB SL] GROUP BY
(Year([Date])*12+Month([Date])-1), (Format([Date],"mmm"" '""yy")) PIVOT
[DMRDB SL].CCID;

-JS

I am familiar with NZ. If the vendor code is the Group By Column Heading,
and the query comes up with no data, how would you invoke the NZ
function?
TIA

Assuming that you are plotting data "per month" or similar you need to
create a table that has all months represented and then outer join to that
in your query so that all months are represented even when the other tables
have no data.

A caveat here is that you need to consider the type of data you are
charting before you do this. If you are just charting deliveries or
delivery amounts, then it should be harmless. However; if this were a QA
database and you were charting rejects or late deliveries, then you
wouldn't want months with no activity to be plotted the same as months with
activity, but zero instances. A vendor's performance should not be
bolstered up merely by having months where he has done nothing.