I have a crosstab query that shows null values. I would like it to show zero in place of the null values. I have tried the nz and still get null values.
Code:
TRANSFORM Nz(Sum([CalculatedPoints])) AS Total
SELECT tblDivisionPoints.DivisionName, tblHorse.HorseName, tblPeople.PeopleName
FROM (((qryFinalShowPointCalc_AllShows INNER JOIN tblDivisionPoints ON qryFinalShowPointCalc_AllShows.pkDivisionPts = tblDivisionPoints.pkDivisionPts) INNER JOIN tblShow ON qryFinalShowPointCalc_AllShows.fkShowNum = tblShow.ShowNum_pk) INNER JOIN tblHorse ON qryFinalShowPointCalc_AllShows.fkHorseNum = tblHorse.HorseNum_pk) INNER JOIN tblPeople ON qryFinalShowPointCalc_AllShows.fkRiderNum = tblPeople.PeopleNum_pk
GROUP BY tblDivisionPoints.Order, tblDivisionPoints.DivisionName, tblHorse.HorseName, tblPeople.PeopleName, qryFinalShowPointCalc_AllShows.CalculatedPoints
ORDER BY tblShow.ShowName
PIVOT tblShow.ShowName;
I also tried adding zero to the field.
Code:
TRANSFORM Sum([CalculatedPoints]+0) AS Total
SELECT tblDivisionPoints.DivisionName, tblHorse.HorseName, tblPeople.PeopleName
FROM (((qryFinalShowPointCalc_AllShows INNER JOIN tblDivisionPoints ON qryFinalShowPointCalc_AllShows.pkDivisionPts = tblDivisionPoints.pkDivisionPts) INNER JOIN tblShow ON qryFinalShowPointCalc_AllShows.fkShowNum = tblShow.ShowNum_pk) INNER JOIN tblHorse ON qryFinalShowPointCalc_AllShows.fkHorseNum = tblHorse.HorseNum_pk) INNER JOIN tblPeople ON qryFinalShowPointCalc_AllShows.fkRiderNum = tblPeople.PeopleNum_pk
GROUP BY tblDivisionPoints.Order, tblDivisionPoints.DivisionName, tblHorse.HorseName, tblPeople.PeopleName, qryFinalShowPointCalc_AllShows.CalculatedPoints
ORDER BY tblShow.ShowName
PIVOT tblShow.ShowName;
I get the same results. Any suggestions?
Thank you