Crosstab Query

[BenUK]

I have a table which contains names, dates, and data. I then query this table with a crosstab query, the crosstab query has the Name set as Row and the Date set as Column. The crosstab query displays every name in the table and every date (there are no gaps here), however as some names don't have attributable data on some dates, There are blanks in the values. I want these blanks to appear as 0. The query I have is as follows:

TRANSFORM Last(tblShiftStarts.[Start Time]) AS [LastOfStart Time]
SELECT agent.AGENT_NAME
FROM tblShiftStarts INNER JOIN agent ON tblShiftStarts.AGENT_ID = agent.AGENT_ID
GROUP BY agent.AGENT_NAME
ORDER BY agent.AGENT_NAME, tblShiftStarts.DATE
PIVOT tblShiftStarts.DATE;

I have tried the nz function on date, and it doesn't have the required effect, all the date column headers just appear as two unprintable characters. I have also tried it on the data again not the required effect, nothing happens!

I'd really appreciate it if anyone can offer any advice, I've checked other forums for similar questions and have got the same answer every time - people suggest to use the nz function, but no one can get it to work???

Thanks in advance
Ben