Display 0 instead of 1 for 'Date of Exit' in my report

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • kashif73
    New Member
    • Sep 2008
    • 91
    #1

    Display 0 instead of 1 for 'Date of Exit' in my report

    Hi, I have a crosstab query which feeds into my report. I'm keeping track of leave requests for one of my office locations. The staff have leave for lets say 10 days of a month from 10 Feb (Date of Dep ) - 20 Feb (Date of Arrival ). 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 leave, in this case on 20th Feb, their date of arrival. Can anyone let me know please how to do it. Below is my crosstab query :

    Code:
    TRANSFORM Nz(Count(tblMaster_TEMP.keyMasterID),0) AS AvgOfkeyMasterID
SELECT tblMaster_TEMP.[Mission Status] AS Msn, (tblMaster_TEMP.OrgName) AS Department, tblMaster_TEMP.FullName AS [Staff Name], tblMaster_TEMP.Location, tblMaster_TEMP.[Date of Departure] AS [Start of Leave], tblMaster_TEMP.[Date of Arrival] AS [End of Leave]
FROM tblMaster_TEMP
WHERE (((tblMaster_TEMP.[Date of Departure]) 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.[Date of Departure], tblMaster_TEMP.[Date of Arrival]
PIVOT Format([Date of Departure],'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.
    Tags: None
    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834
      #2
      For each Employee, assuming the From and To Dates are displayed in a Linear fashion, then you can create a Calculated Field that will display a Zero (0) where they both are equal, and 1 where they are not, as in:
      Code:
      SELECT tbl_employees_TEMP.FullName, tbl_employees_TEMP.LeaveDateFrom, _
tbl_employees_TEMP.LeaveDateTo, tbl_employees_TEMP.Status, _
IIf([LeaveDateFrom]=[LeaveDateTo],0,1) AS LCode _
FROM tbl_employees_TEMP;
      P.S. - Sorry, no time for Column Formatting!
      Code:
      FullName	LeaveDateFrom	LeaveDateTo	Status	LCode
Fred	11/22/2009	11/30/2009	1	1
Fred	11/23/2009	11/30/2009	1	1
Fred	11/24/2009	11/30/2009	1	1
Fred	11/25/2009	11/30/2009	1	1
Fred	11/26/2009	11/30/2009	1	1
Fred	11/27/2009	11/30/2009	1	1
Fred	11/28/2009	11/30/2009	1	1
Fred	11/29/2009	11/30/2009	1	1
Fred	11/30/2009	11/30/2009	1	0
Barney	10/4/2009	10/10/2009	1	1
Barney	10/5/2009	10/10/2009	1	1
Barney	10/6/2009	10/10/2009	1	1
Barney	10/7/2009	10/10/2009	1	1
Barney	10/8/2009	10/10/2009	1	1
Barney	10/9/2009	10/10/2009	1	1
Barney	10/10/2009	10/10/2009	1	0
Wilma	11/12/2009	11/22/2009	1	1
Wilma	11/13/2009	11/22/2009	1	1
Wilma	11/14/2009	11/22/2009	1	1
Wilma	11/15/2009	11/22/2009	1	1
Wilma	11/16/2009	11/22/2009	1	1
Wilma	11/17/2009	11/22/2009	1	1
Wilma	11/18/2009	11/22/2009	1	1
Wilma	11/19/2009	11/22/2009	1	1
Wilma	11/20/2009	11/22/2009	1	1
Wilma	11/21/2009	11/22/2009	1	1
Wilma	11/22/2009	11/22/2009	1	0
Betty	10/19/2009	11/2/2009	1	1
Betty	10/20/2009	11/2/2009	1	1
Betty	10/21/2009	11/2/2009	1	1
Betty	10/22/2009	11/2/2009	1	1
Betty	10/23/2009	11/2/2009	1	1
Betty	10/24/2009	11/2/2009	1	1
Betty	10/25/2009	11/2/2009	1	1
Betty	10/26/2009	11/2/2009	1	1
Betty	10/27/2009	11/2/2009	1	1
Betty	10/28/2009	11/2/2009	1	1
Betty	10/29/2009	11/2/2009	1	1
Betty	10/30/2009	11/2/2009	1	1
Betty	10/31/2009	11/2/2009	1	1
Betty	11/1/2009	11/2/2009	1	1
Betty	11/2/2009	11/2/2009	1	0
Julie	11/17/2009	11/20/2009	1	1
Julie	11/18/2009	11/20/2009	1	1
Julie	11/19/2009	11/20/2009	1	1
Julie	11/20/2009	11/20/2009	1	0
Bam-Bam	12/1/2009	12/10/2009	1	1
Bam-Bam	12/2/2009	12/10/2009	1	1
Bam-Bam	12/3/2009	12/10/2009	1	1
Bam-Bam	12/4/2009	12/10/2009	1	1
Bam-Bam	12/5/2009	12/10/2009	1	1
Bam-Bam	12/6/2009	12/10/2009	1	1
Bam-Bam	12/7/2009	12/10/2009	1	1
Bam-Bam	12/8/2009	12/10/2009	1	1
Bam-Bam	12/9/2009	12/10/2009	1	1
Bam-Bam	12/10/2009	12/10/2009	1	0
Aziz Khan	10/12/2009	10/15/2009	1	1
Aziz Khan	10/13/2009	10/15/2009	1	1
Aziz Khan	10/14/2009	10/15/2009	1	1
Aziz Khan	10/15/2009	10/15/2009	1	0
Rubik	10/29/2009	11/4/2009	1	1
Rubik	10/30/2009	11/4/2009	1	1
Rubik	10/31/2009	11/4/2009	1	1
Rubik	11/1/2009	11/4/2009	1	1
Rubik	11/2/2009	11/4/2009	1	1
Rubik	11/3/2009	11/4/2009	1	1
Rubik	11/4/2009	11/4/2009	1	0
Bam-Bam	11/4/2009	11/23/2009	1	1
Bam-Bam	11/5/2009	11/23/2009	1	1
Bam-Bam	11/6/2009	11/23/2009	1	1
Bam-Bam	11/7/2009	11/23/2009	1	1
Bam-Bam	11/8/2009	11/23/2009	1	1
Bam-Bam	11/9/2009	11/23/2009	1	1
Bam-Bam	11/10/2009	11/23/2009	1	1
Bam-Bam	11/11/2009	11/23/2009	1	1
Bam-Bam	11/12/2009	11/23/2009	1	1
Bam-Bam	11/13/2009	11/23/2009	1	1
Bam-Bam	11/14/2009	11/23/2009	1	1
Bam-Bam	11/15/2009	11/23/2009	1	1
Bam-Bam	11/16/2009	11/23/2009	1	1
Bam-Bam	11/17/2009	11/23/2009	1	1
Bam-Bam	11/18/2009	11/23/2009	1	1
Bam-Bam	11/19/2009	11/23/2009	1	1
Bam-Bam	11/20/2009	11/23/2009	1	1
Bam-Bam	11/21/2009	11/23/2009	1	1
Bam-Bam	11/22/2009	11/23/2009	1	1
Bam-Bam	11/23/2009	11/23/2009	1	0

        Comment

        • kashif73
          New Member
          • Sep 2008
          • 91
          #3
          Thxs ADezii. Your help is very much appreciated, again :). Best regards,

          Kashif

            Comment

            Previous template Next
            Working...