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" <(E-Mail Removed)> wrote in message
news:012D751A-CB0D-4489-A82C-(E-Mail Removed)...
> 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.
>
>
>
Similar Threads
