I have a crosstab query where the column names are changing every Year.
The report is for getting totals for past 3 years + the year I input.
eg. If I input 2001 the report will be for 1998, 1999, 2000 and 2001
I have created a crosstab query which I am referring to in codes below
Set rst = CurrentDb.OpenRecordset("select * FROM Form6_Crosstab")
If Not rst.EOF Then
Do Until rst.EOF
If IsNull(rst![NAME OF THE COLUMN]) = False Then
'PY1 = PY1 + rst![NAME OF THE COLUMN]
EndDtYr = Format(Forms![IARpts]![EndingDate], "YYYY")
CurrentYear = EndDtYr
Year1 = EndDtYr - 1 (year 1 before the input date)
Year2 = EndDtYr - 2 (year 2 before the input date)
Year3 = EndDtYr - 3 (year 3 before the input date)
EndDtYr is a text field where I enter the current year which the report is based on then I can get the past 3 year in the above code. The problem is the column name in the crosstab changes every time when I enter a new year. How can I refer to the column Names by code. eg. rst![Year1] etc. the name year1 will depends on the year I enter.
WHAT AM I DOING WRONG
Can any one Please help