If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Crosstab Report's subreport
I have a report based on the following code:
Private Sub Report_Open(Cancel As Integer) On Error GoTo Err_Report_Open Dim dStart, DayOne, DayTwo, DayThree, DayFour, DayFive, DaySix, DaySeven As Variant Dim stSql String dStart = Forms!PrintReport.Friday DayOne = dStart DayTwo = DateAdd("d", 1, DayOne) DayThree = DateAdd("d", 2, DayOne) DayFour = DateAdd("d", 3, DayOne) DayFive = DateAdd("d", 4, DayOne) DaySix = DateAdd("d", 5, DayOne) DaySeven = DateAdd("d", 6, DayOne) stSql = "TRANSFORM Max(MeritsMain2Total.Total) AS MaxOfTotal SELECT Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm FROM Dorms INNER JOIN ((Phase INNER JOIN (CadetsName INNER JOIN Cadets ON CadetsName.CadetID = Cadets.CadetID) ON Phase.PhaseID = Cadets.PhaseID) INNER JOIN MeritsMain2Total ON Cadets.CadetID = MeritsMain2Total.CadetID) ON (Dorms.DormID = CadetsName.DormID) AND (Dorms.DormID = Cadets.DormID) GROUP BY Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm PIVOT Format([DTGofMerits],'Short Date') In ('" & DayOne & "','" & DayTwo & "','" & DayThree & "','" & DayFour & "','" & DayFive & "','" & DaySix & "','" & DaySeven & "');" Me.RecordSource = stSql Me.DayOne.ControlSource = DayOne Me.DayOneLabel.Caption = DayOne Me.DayTwo.ControlSource = DayTwo Me.DayTwoLabel.Caption = DayTwo Me.DayThree.ControlSource = DayThree Me.DayThreeLabel.Caption = DayThree Me.DayFour.ControlSource = DayFour Me.DayFourLabel.Caption = DayFour Me.DayFive.ControlSource = DayFive Me.DayFiveLabel.Caption = DayFive Me.DaySix.ControlSource = DaySix Me.DaySixLabel.Caption = DaySix Me.DaySeven.ControlSource = DaySeven Me.DaySevenLabel.Caption = DaySeven End_Report_Open: Exit Sub Err_Report_Open: MsgBox Err.Description Resume End_Report_Open End Sub And believe it or not, it works. The report is based on a saved crosstab, but I wanted to limit the fields dynamically. I also have a subreport that is based on a saved query("SELECT DISTINCTROW CadetAwards.CadetID, AwardType.Abbrev, Count(CadetAwards.AwardType) AS CountOfAwardType FROM CadetAwards INNER JOIN AwardType ON CadetAwards.AwardType = AwardType.AwardType GROUP BY CadetAwards.CadetID, AwardType.Abbrev, AwardType.AwardType HAVING (((([AwardType].[AwardType])=6 Xor ([AwardType].[AwardType])=10)False));"). Now I thought that I could just easily set the link fields to show linked information (CadetID-CadetID), but when I run the report, the subreport does not show any information. How do I get the subreport to show? I understand that if there is not data to show that it should be blank, but there is data. |
#2
|
|||
|
|||
Crosstab Report's subreport
It looks like you might have a crosstab that uses a week of dates as columns
headings. If this is the case, I think the solution at http://www.tek-tips.com/faqs.cfm?fid=5466 is much more efficient and simpler (no code required). It should work well as a subreport without having to change any Record Sources. -- Duane Hookom MS Access MVP "Joseph" wrote in message ... I have a report based on the following code: Private Sub Report_Open(Cancel As Integer) On Error GoTo Err_Report_Open Dim dStart, DayOne, DayTwo, DayThree, DayFour, DayFive, DaySix, DaySeven As Variant Dim stSql String dStart = Forms!PrintReport.Friday DayOne = dStart DayTwo = DateAdd("d", 1, DayOne) DayThree = DateAdd("d", 2, DayOne) DayFour = DateAdd("d", 3, DayOne) DayFive = DateAdd("d", 4, DayOne) DaySix = DateAdd("d", 5, DayOne) DaySeven = DateAdd("d", 6, DayOne) stSql = "TRANSFORM Max(MeritsMain2Total.Total) AS MaxOfTotal SELECT Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm FROM Dorms INNER JOIN ((Phase INNER JOIN (CadetsName INNER JOIN Cadets ON CadetsName.CadetID = Cadets.CadetID) ON Phase.PhaseID = Cadets.PhaseID) INNER JOIN MeritsMain2Total ON Cadets.CadetID = MeritsMain2Total.CadetID) ON (Dorms.DormID = CadetsName.DormID) AND (Dorms.DormID = Cadets.DormID) GROUP BY Cadets.CadetID, MeritsMain2Total.CadetName, Phase.Phase, Dorms.Dorm PIVOT Format([DTGofMerits],'Short Date') In ('" & DayOne & "','" & DayTwo & "','" & DayThree & "','" & DayFour & "','" & DayFive & "','" & DaySix & "','" & DaySeven & "');" Me.RecordSource = stSql Me.DayOne.ControlSource = DayOne Me.DayOneLabel.Caption = DayOne Me.DayTwo.ControlSource = DayTwo Me.DayTwoLabel.Caption = DayTwo Me.DayThree.ControlSource = DayThree Me.DayThreeLabel.Caption = DayThree Me.DayFour.ControlSource = DayFour Me.DayFourLabel.Caption = DayFour Me.DayFive.ControlSource = DayFive Me.DayFiveLabel.Caption = DayFive Me.DaySix.ControlSource = DaySix Me.DaySixLabel.Caption = DaySix Me.DaySeven.ControlSource = DaySeven Me.DaySevenLabel.Caption = DaySeven End_Report_Open: Exit Sub Err_Report_Open: MsgBox Err.Description Resume End_Report_Open End Sub And believe it or not, it works. The report is based on a saved crosstab, but I wanted to limit the fields dynamically. I also have a subreport that is based on a saved query("SELECT DISTINCTROW CadetAwards.CadetID, AwardType.Abbrev, Count(CadetAwards.AwardType) AS CountOfAwardType FROM CadetAwards INNER JOIN AwardType ON CadetAwards.AwardType = AwardType.AwardType GROUP BY CadetAwards.CadetID, AwardType.Abbrev, AwardType.AwardType HAVING (((([AwardType].[AwardType])=6 Xor ([AwardType].[AwardType])=10)False));"). Now I thought that I could just easily set the link fields to show linked information (CadetID-CadetID), but when I run the report, the subreport does not show any information. How do I get the subreport to show? I understand that if there is not data to show that it should be blank, but there is data. |
Thread Tools | |
Display Modes | |
|
|