Report on Crosstab Query

Can anyone please help with this problem? I have a report based on a
crosstab query where columns are years going 9 years into the past including
the current one. i.e.. 1997, 1998, 1999, etc. The fields on the report that
are bound to the data appear to be "hard-coded" and need changing at the end
of every year. I have written code to load the labels, etc. with current
info, but is there a way to make the report's text boxes be bound to current
data as well?

Thanks in advance
--
Glenn

Glenn Suggs wrote:

Can anyone please help with this problem? I have a report based on a
crosstab query where columns are years going 9 years into the past including
the current one. i.e.. 1997, 1998, 1999, etc. The fields on the report that
are bound to the data appear to be "hard-coded" and need changing at the end
of every year. I have written code to load the labels, etc. with current
info, but is there a way to make the report's text boxes be bound to current
data as well?


Change the query's PIVOT clause to use:
"Y" & (Year(Date) - Year(datefield))

Then the report text boxes can be bound to the field names
M0, M1, ...

--
Marsh
MVP [MS Access]

I tried this and all the columns that had years disappeared. They became one
column with Y0 at the top. Is this the correct syntax for the PIVOT clause?
PIVOT "Y" & (Year(Date)-Year([DATE])) where [DATE] is my date field...

"Marshall Barton" wrote:

Glenn Suggs wrote:

Can anyone please help with this problem? I have a report based on a
crosstab query where columns are years going 9 years into the past including
the current one. i.e.. 1997, 1998, 1999, etc. The fields on the report that
are bound to the data appear to be "hard-coded" and need changing at the end
of every year. I have written code to load the labels, etc. with current
info, but is there a way to make the report's text boxes be bound to current
data as well?


Change the query's PIVOT clause to use:
"Y" & (Year(Date) - Year(datefield))

Then the report text boxes can be bound to the field names
M0, M1, ...

--
Marsh
MVP [MS Access]

You don't really have a field named DATE, do you? Since
that is the name of the Date function, you may be running
into a name conflict here.

OTOH, I left out the parenthesis, it should be:
PIVOT "Y" & (Year(Date())-Year([DATE]))
--
Marsh
MVP [MS Access]



Glenn Suggs wrote:
I tried this and all the columns that had years disappeared. They became one
column with Y0 at the top. Is this the correct syntax for the PIVOT clause?
PIVOT "Y" & (Year(Date)-Year([DATE])) where [DATE] is my date field...


Glenn Suggs wrote:
Can anyone please help with this problem? I have a report based on a
crosstab query where columns are years going 9 years into the past including
the current one. i.e.. 1997, 1998, 1999, etc. The fields on the report that
are bound to the data appear to be "hard-coded" and need changing at the end
of every year. I have written code to load the labels, etc. with current
info, but is there a way to make the report's text boxes be bound to current
data as well?


"Marshall Barton" wrote:
Change the query's PIVOT clause to use:
"Y" & (Year(Date) - Year(datefield))

Then the report text boxes can be bound to the field names
M0, M1, ...

I believe Marsh meant (note the additional ()s):
PIVOT "Y" & (Year(Date())-Year([DATE]))

--
Duane Hookom
MS Access MVP
--

"Glenn Suggs" wrote in message
...
I tried this and all the columns that had years disappeared. They became
one
column with Y0 at the top. Is this the correct syntax for the PIVOT
clause?
PIVOT "Y" & (Year(Date)-Year([DATE])) where [DATE] is my date
field...

"Marshall Barton" wrote:

Glenn Suggs wrote:

Can anyone please help with this problem? I have a report based on a
crosstab query where columns are years going 9 years into the past
including
the current one. i.e.. 1997, 1998, 1999, etc. The fields on the report
that
are bound to the data appear to be "hard-coded" and need changing at the
end
of every year. I have written code to load the labels, etc. with
current
info, but is there a way to make the report's text boxes be bound to
current
data as well?


Change the query's PIVOT clause to use:
"Y" & (Year(Date) - Year(datefield))

Then the report text boxes can be bound to the field names
M0, M1, ...

--
Marsh
MVP [MS Access]

Many thanks. This has helped to resolve my problem. I'm now getting all the
correct values in the correct rows and columns again with Y0...Y8 as column
headings. I can take it from there.

Thanks again,
Glenn

"Marshall Barton" wrote:

You don't really have a field named DATE, do you? Since
that is the name of the Date function, you may be running
into a name conflict here.

OTOH, I left out the parenthesis, it should be:
PIVOT "Y" & (Year(Date())-Year([DATE]))
--
Marsh
MVP [MS Access]



Glenn Suggs wrote:
I tried this and all the columns that had years disappeared. They became one
column with Y0 at the top. Is this the correct syntax for the PIVOT clause?
PIVOT "Y" & (Year(Date)-Year([DATE])) where [DATE] is my date field...


Glenn Suggs wrote:
Can anyone please help with this problem? I have a report based on a
crosstab query where columns are years going 9 years into the past including
the current one. i.e.. 1997, 1998, 1999, etc. The fields on the report that
are bound to the data appear to be "hard-coded" and need changing at the end
of every year. I have written code to load the labels, etc. with current
info, but is there a way to make the report's text boxes be bound to current
data as well?


"Marshall Barton" wrote:
Change the query's PIVOT clause to use:
"Y" & (Year(Date) - Year(datefield))

Then the report text boxes can be bound to the field names
M0, M1, ...