Report on Crosstab Query

[Guest]

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

 

[Marshall Barton]

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, ...

 

[Guest]

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]

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]

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...

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, ...

 

[Duane Hookom]

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

--
Duane Hookom
MS Access MVP
--

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...

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, ...

 

[Guest]

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

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]

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?

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, ...