Dynamic Report

[AccessTexas]

I have a report where I would like to show YTD Sales in comparison to the last seven years by the current month in each year.

March
Company A Company B
2001 100 100
2002 150 125
2003 225 160

I'm using a select case that references the month number picked when you open the report. The data is all in the query in twelve fields but I want to just call out the data listed for one month.

I tried to using an unbound textbox and changing to data the following way but it doesn't work. I quess what I'd like to know if this can be tweaked to work or am I violating rules that won't allow this.

Private Sub Report_Open(Cancel As Integer)

Dim x As Variant
x = InputBox("Which Month Number Do You Wish To Have?", "ABC Company Reports")
Select Case x
Case 1: lblMonth.Caption = "JANUARY"
Me.txtA.ControlSource = Me.YTDa_x.Value
Me.txtB.ControlSource = Me.YTDb_x.Value
Case 2: lblMonth.Caption = "FEBRUARY"
Me.txtA.ControlSource = Me.YTDa_x.Value
Me.txtB.ControlSource = Me.YTDb_x.Value
Case 3: lblMonth.Caption = "MARCH"
Me.txtA.ControlSource = Me.YTDa_x.Value
Me.txtB.ControlSource = Me.YTDb_x.Value
......
End Select
End Sub

 

[Moniker]

Set that up completely separate of the report, in its own query. It will look something like this:

Year    Month    Company1    Company2    CompanyX
2000    1        100         125         xxx
2000    2        200         190         xxx
200x    x        xxx         xxx         xxx

Then just do a simple SQL statement as the report's record source, something like this:

SELECT * FROM Query_With_All_Data WHERE Month=Parameter_Value;

 

[AccessTexas]

Thank you, it was very helpful. I used to do this in Excel so the process is usually turned around a bit in Access.