Report on Crosstab Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Glenn said:
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?
Click to expand...


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 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...
 
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 said:
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 said:
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, ...
Click to expand...
Click to expand...
 
I believe Marsh meant (note the additional ()s):
PIVOT "Y" & (Year(Date())-Year([DATE]))

--
Duane Hookom
MS Access MVP
--

Glenn Suggs said:
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 said:
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, ...
Click to expand...
Click to expand...
 
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 said:
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 said:
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?
Click to expand...
Marshall Barton said:
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, ...
Click to expand...
Click to expand...
Click to expand...
 
Back
Top