Crosstab Report using 'last 6 weeks' as column headings

[Williams485]

Hello Access Forum,



I've created a report that has a crosstab query as its record source:

TRANSFORM Count([ContHist Static].RECID) AS CountOfRECID
SELECT Lookups.DESCRIPTION
FROM [ContHist Static], Lookups
WHERE ((([ContHist Static]![ACTVCODE])=[Lookups]![FLD_VAL]) AND (([ONDATE]-DatePart("w",[ONDATE])+2) Between (Date()-42) And Date()))
GROUP BY Lookups.DESCRIPTION
ORDER BY [ONDATE]-DatePart("w",[ONDATE])+2 DESC 
PIVOT [ONDATE]-DatePart("w",[ONDATE])+2;

this is what the query gives me:

DESCRIPTION	23/02/2015	16/02/2015	09/02/2015	02/02/2015	26/01/2015	19/01/2015
Item 1						2
Item 2		1	2	2	7	8
Item 3	1	6	2	1	2	1
Item 4	6	22	10	7	5	2
Item 5	6	22	10	7	5	2
Item 6					1	1
Item 7			1			1
Item 8		2	1	4	2	3
Item 9		3	2	1	3	1
Item 10		2		2
Item 11					1
Item 12	1	6		2		1
Item 13						1

I created the report by simply 'dragging' the query onto the report in design view. This worked fine until the date changed: it fixed the column headers and control sources of the text boxes and doesn't update with the query (i.e. it fixed the right-most column as 12/01/15 but that is no longer within the scope of the query and gives errors).

How can I set the report so that the controls (and their labels) adjust according to what the query actually shows? (I've tried looking for solutions to this but I don't understand any of what I have found).

Many thanks

[orange]

I think you have to include something along these lines.
Date() and DateAdd("ww",-6, Date())

where Date is today and DateAdd("ww",-6, Date()) is 6 weeks earlier.

But you know your situation better than the readers do.
Good luck

[Williams485]

Thanks, Orange. Can you be clearer as to where I need to include your suggestion? Is it the column headers or the value fields on the report?

[Williams485]

Solved: I ended up changing both the label and control source of the text boxes using VBA:

Private Sub Report_Load()
Me.lblWk1.Caption = CStr(Date - DatePart("w", Date) + 2)
Me.lblWk2.Caption = CStr((Date - DatePart("w", Date) + 2) - 7)
Me.lblWk3.Caption = CStr((Date - DatePart("w", Date) + 2) - 14)

Me.txtWk1.ControlSource = CStr(Me.lblWk1.Caption)
Me.txtWk2.ControlSource = CStr(Me.lblWk2.Caption)
Me.txtWk3.ControlSource = CStr(Me.lblWk3.Caption)
End Sub