Nulls to Zero 1

[xianman]

I have run into this problem in repeated instances. I am trying to get query results that are null to return a zero value so I can chart them. I am using Access 97. I have heard rumors about a subquery working, but have failed in my attempts. I don't know if this will help, but here is SQL. This is a qry just to get a chart to have connected lines in a report instead of spaces where there are nulls.


PARAMETERS [forms]![frmmonth]![branch] Text;
SELECT tblYear.Year, tblYear.Month, Sum(qrymonth.Placements) AS Placements
FROM qrymonth RIGHT JOIN tblYear ON (qrymonth.Month = tblYear.Month) AND (qrymonth.Year = tblYear.Year)
GROUP BY tblYear.Year, tblYear.Month
HAVING (((tblYear.Year) Between [forms]![frmmonth]![beginning] And [forms]![frmmonth]![end]));

p.s. Had to enclude the parameters to get the chart to work.

 

[dhookom]

Try:

PARAMETERS [forms]![frmmonth]![branch] Text;
SELECT tblYear.Year, tblYear.Month, Val(Nz(Sum(qrymonth.Placements),0)) AS Placements
FROM qrymonth RIGHT JOIN tblYear ON (qrymonth.Month = tblYear.Month) AND (qrymonth.Year = tblYear.Year)
GROUP BY tblYear.Year, tblYear.Month
HAVING (((tblYear.Year) Between [forms]![frmmonth]![beginning] And [forms]![frmmonth]![end]));


Duane
MS Access MVP

 

[cghoga]

before you execute your query above can you execute some code that sets Null values to 0?

Like this...

DoCmd.RunSQL "UPDATE YourTableName SET YourFieldName = 0
WHERE YourFieldName Is Null"


hope this helps.

 

[dhookom]

This might not be possible since there are probably no records to update. The query is a RIGHT JOIN so there are most likely months that didn't have corresponding records in qryMonth.

Duane
MS Access MVP