REPORT IS RUN FROM THIS ACTION. 


Private Sub Command80_Click()

Dim db As Database

Dim rsOpen As Recordset
Dim rsClosed As Recordset
Dim rsPast As Recordset
Dim rsTemp As Recordset

Dim qdfOpen As QueryDef
Dim qdfClosed As QueryDef
Dim qdfPast As QueryDef
Dim SumDeptId As Single

Dim i As Integer

Dim OpenDate As String
Dim CloseDate As String
Dim sYearStart As String
Dim dYearStart As Date
Dim mCycle As Integer
Dim mflag As Boolean



If Val(Me.mYear) > 2100 Or Val(Me.mYear) < 2000 Then
    MsgBox "You must enter a vaild year", vbCritical
    Exit Sub
End If


Call delTblData("tblcorpTemp")

OpenDate = mStartMonth(1, Me.mMonth, Me.mYear)
CloseDate = DateAdd("m", 1, mStartMonth(1, Me.mMonth, Me.mYear)) - 1
sYearStart = "1" & "/" & "1" & "/" & mYear
dYearStart = CDate(sYearStart)



Me.Start = OpenDate
Me.End = CloseDate

Set db = CurrentDb

Set qdfOpen = db.QueryDefs("qryOpenDatesSumAll")
Set qdfClosed = db.QueryDefs("qryClosedDatesSumAll")
Set qdfPast = db.QueryDefs("qryPastDueDatesSumAll")
Set rsTemp = db.OpenRecordset("tblCorpTemp", dbOpenDynaset)


For mCycle = 1 To 2

    If mCycle = 2 Then
        OpenDate = dYearStart
    CloseDate = Int(Now())
    End If
    
    With qdfOpen
        .Parameters(0) = OpenDate
        .Parameters(1) = CloseDate
        Set rsOpen = .OpenRecordset
    End With

    With qdfClosed
        .Parameters(0) = OpenDate
        .Parameters(1) = CloseDate
        Set rsClosed = .OpenRecordset
    End With

    With qdfPast
        .Parameters(0) = OpenDate
        .Parameters(1) = CloseDate
        Set rsPast = .OpenRecordset
    End With

rsOpen.MoveFirst
rsClosed.MoveFirst
rsPast.MoveFirst
With rsTemp

If mCycle = 1 Then
          
        For i = 1 To rsOpen.RecordCount
            SumDeptId = rsOpen!deptId + rsClosed!deptId + rsPast!deptId
            If Int(SumDeptId / 3) <> SumDeptId / 3 Then
                MsgBox "Records are not being calculated correctly"
            Exit Sub
            End If
            Debug.Print rsOpen!deptId & ", " & rsClosed!deptId & ", " & rsPast!deptId
            
            .AddNew
            !deptId = rsOpen!deptId
            !Open = rsOpen!Open
            !Closed = rsClosed!Closed
            !Past = rsPast!Past
            !Days = rsClosed!totaldays
            .Update
            rsOpen.MoveNext
            rsClosed.MoveNext
            rsPast.MoveNext
           
       
        Next i
    

Else
   
        
        .MoveFirst
        For i = 1 To rsOpen.RecordCount
            
             Debug.Print rsOpen!deptId & "," & rsClosed!deptId & ", " & rsPast!deptId
            .Edit
            !OpenYTD = rsOpen!Open
            !ClosedYTD = rsClosed!Closed
            !PastYTD = rsPast!Past
            !DaysYTD = rsClosed!totaldays
            .Update
            .MoveNext
            rsOpen.MoveNext
            rsClosed.MoveNext
            rsPast.MoveNext
        Next i
    
End If
End With

Set rsOpen = Nothing
Set rsClosed = Nothing
Set rsPast = Nothing


Next mCycle


Set rsTemp = Nothing
Set rsOpen = Nothing
Set rsClosed = Nothing
Set rsPast = Nothing
Set qdfOpen = Nothing
Set qdfClosed = Nothing
Set qdfPast = Nothing
Set db = Nothing

DoCmd.OpenReport "rptCorp", acViewPreview

End Sub



HERE ARE THE QUERY DETAILS 


qryClosedDatesSumAll
--------------------
SELECT tblDepartments.DeptId, qryClosedDatesSum.Closed, qryClosedDatesSum.TotalDays
FROM tblDepartments LEFT JOIN qryClosedDatesSum ON tblDepartments.DeptId = qryClosedDatesSum.DeptId
ORDER BY tblDepartments.DeptId;

qryOpenDatesSummAll
--------------------
SELECT tblDepartments.DeptId, qryOpenDatesSum.Open
FROM qryOpenDatesSum RIGHT JOIN tblDepartments ON qryOpenDatesSum.DeptId = tblDepartments.DeptId
ORDER BY tblDepartments.DeptId;

qryPastDatesSumAll
------------------
SELECT tblDepartments.DeptId, qryPastDueDatesSum.Past
FROM tblDepartments LEFT JOIN qryPastDueDatesSum ON tblDepartments.DeptId = qryPastDueDatesSum.deptId
ORDER BY tblDepartments.DeptId;