Dynamic Report

AccessTexas

Registered User.
Local time
Yesterday, 21:02
Joined
Dec 3, 2005
Messages
18
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
 
Set that up completely separate of the report, in its own query. It will look something like this:

Code:
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:

Code:
SELECT * FROM Query_With_All_Data WHERE Month=Parameter_Value;
 
Thank you, it was very helpful. I used to do this in Excel so the process is usually turned around a bit in Access.
 

Users who are viewing this thread

Back
Top Bottom