Display 0 instead of 1 for 'Date of Exit' in my crosstab query

[kashifmalick]

Hi, I have a crosstab query which feeds into my report. I'm keeping track of tours for one of my office locations. The staff have visit for lets say 10 days of a month from 10 Feb (Date of Arrival ) - 20 Feb (Date of Exit ). These 10 days are displayed as 1's in my query & report. What I want is to display a '0' at the last day of visit, in this case on 20th Feb, their date of exit. Can anyone let me know please how to do it. Below is my crosstab query :

TRANSFORM Nz(Count(tblMaster_TEMP.keyMasterID),0) AS AvgOfkeyMasterID
SELECT tblMaster_TEMP.[Mission Status] AS Msn, (tblMaster_TEMP.OrgName) AS Agency, tblMaster_TEMP.FullName AS [Staff Name], tblMaster_TEMP.Location, tblMaster_TEMP.[Mission Start] AS [Arrival Date], tblMaster_TEMP.[Date of Exit] AS [Exit Date]
FROM tblMaster_TEMP
WHERE (((tblMaster_TEMP.[Date of Arrival]) Between #2/1/2010# And #2/28/2010# And tblMaster_TEMP.Location='Islamabad'))
GROUP BY tblMaster_TEMP.OrgName, tblMaster_TEMP.[Mission Status], tblMaster_TEMP.FullName, tblMaster_TEMP.Location, tblMaster_TEMP.[Mission Start], tblMaster_TEMP.[Date of Exit]
PIVOT Format([Date of Arrival],'Short Date') In (2/1/2010,2/2/2010,2/3/2010,2/4/2010,2/5/2010,2/6/2010,2/7/2010,
2/8/2010,2/9/2010,2/10/2010,2/11/2010,2/12/2010,2/13/2010,
2/14/2010,2/15/2010,2/16/2010,2/17/2010,2/18/2010,
2/19/2010,2/20/2010,2/21/2010,2/22/2010,2/23/2010,
2/24/2010,2/25/2010,2/26/2010,2/27/2010,2/28/2010);

Many thanks for helping me out.

 

[kashifmalick]

can anyone please help, need it urgently.....