By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,469 Members | 2,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,469 IT Pros & Developers. It's quick & easy.

Variable # of columns being output by a Crosstab query

P: n/a
....and the SELECT thats trying to pull from said Query doesn't like it
one bit! ;)

I'm working on this project (in Access 2002) and there is a report
who's RecordSource is the following SELECT;

SELECT
xTab.FirstOfPRIORITZTN_STATUS_NAME,
xTab.[Total Of CountofITEM_ID],
iif(IsNull( xTab.[FY06 Q1] ), 0, xTab.[FY06 Q1]) AS [FY06 Q1],
iif(IsNull( xTab.[FY06 Q2] ), 0, xTab.[FY06 Q1]) AS [FY06 Q2],
iif(IsNull( xTab.[FY06 Q3] ), 0, xTab.[FY06 Q1]) AS [FY06 Q3],
iif(IsNull( xTab.[FY06 Q4] ), 0, xTab.[FY06 Q1]) AS [FY06 Q4],
iif(IsNull( xTab.[FY07 Q1] ), 0, xTab.[FY06 Q1]) AS [FY07 Q1],
iif(IsNull( xTab.[FY07 Q2] ), 0, xTab.[FY06 Q1]) AS [FY07 Q2],
IsNull( xTab.[<>], 0 ) AS [<>]
FROM
qryAllProjectsEventsPrioritztnDeployment3_Crosstab xTab
WHERE
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Enterprise Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "BU/Specialty Group Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Internal Only")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME="Event Only");

Now, as soon as I try to run the report (Print Preview), I get the "The
Microsoft Jet database engine does not recognize 'xTab.[FY06 Q4]' as a
valid field name or expression."

I've seen a lot of talk on the 'Net about how others who have gotten
this error need to specify the data type(s) of their parameters. Well,
this query doesn't take parameters, it pulls its data from another
query.

After looking at the output of the crosstab query, I can see that
'xTab.[FY06 Q4]' is simply not being output by the query as there is no
data for that column (and the other 2 that follow).

My question is, is there any way to dynamically find out that those
columns are not being output, and deal with it properly so that I can
run this report and not have it crashing?

OR, how should I deal with this? I was thinking of trying to force the
missing columns in there somehow with 0's or something but I just can't
seem to wrap my head around this one.

Thanks for listening,
-S

Jan 21 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
sc************@gmail.com wrote in
news:11*********************@f14g2000cwb.googlegro ups.com:
OR, how should I deal with this? I was thinking of trying to
force the missing columns in there somehow with 0's or
something but I just can't seem to wrap my head around this
one.
from the Access help file on the Crosstab Query .ColumnHeadings
property
:
"If you include a column heading in the ColumnHeadings property
setting, the column is always displayed in query Datasheet view,
even if the column contains no data. This is useful for a report
based on a crosstab query, for example, when you always want to
display the same column headings in the report."

Thanks for listening,
-S


--
Bob Quintal

PA is y I've altered my email address.
Jan 21 '06 #2

P: n/a
Bri

sc************@gmail.com wrote:
...and the SELECT thats trying to pull from said Query doesn't like it
one bit! ;)

I'm working on this project (in Access 2002) and there is a report
who's RecordSource is the following SELECT;

SELECT
xTab.FirstOfPRIORITZTN_STATUS_NAME,
xTab.[Total Of CountofITEM_ID],
iif(IsNull( xTab.[FY06 Q1] ), 0, xTab.[FY06 Q1]) AS [FY06 Q1],
iif(IsNull( xTab.[FY06 Q2] ), 0, xTab.[FY06 Q1]) AS [FY06 Q2],
iif(IsNull( xTab.[FY06 Q3] ), 0, xTab.[FY06 Q1]) AS [FY06 Q3],
iif(IsNull( xTab.[FY06 Q4] ), 0, xTab.[FY06 Q1]) AS [FY06 Q4],
iif(IsNull( xTab.[FY07 Q1] ), 0, xTab.[FY06 Q1]) AS [FY07 Q1],
iif(IsNull( xTab.[FY07 Q2] ), 0, xTab.[FY06 Q1]) AS [FY07 Q2],
IsNull( xTab.[<>], 0 ) AS [<>]
FROM
qryAllProjectsEventsPrioritztnDeployment3_Crosstab xTab
WHERE
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Enterprise Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "BU/Specialty Group Approved")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME = "Internal Only")
Or
(qryAllProjectsEventsPrioritztnDeployment3_Crossta b
.FirstOfPRIORITZTN_STATUS_NAME="Event Only");

Now, as soon as I try to run the report (Print Preview), I get the "The
Microsoft Jet database engine does not recognize 'xTab.[FY06 Q4]' as a
valid field name or expression."

I've seen a lot of talk on the 'Net about how others who have gotten
this error need to specify the data type(s) of their parameters. Well,
this query doesn't take parameters, it pulls its data from another
query.

After looking at the output of the crosstab query, I can see that
'xTab.[FY06 Q4]' is simply not being output by the query as there is no
data for that column (and the other 2 that follow).

My question is, is there any way to dynamically find out that those
columns are not being output, and deal with it properly so that I can
run this report and not have it crashing?

OR, how should I deal with this? I was thinking of trying to force the
missing columns in there somehow with 0's or something but I just can't
seem to wrap my head around this one.

Thanks for listening,
-S


Bob's answer is good if you know ahead of time what columns you are
going to want in the crosstab. However, it is more usual for the
crosstab to be dynamicly creating different columns over time. This
means that the report's query and the report itself need to be capable
of figuring this out and dealing with it.

I have done this before and it is a bit complicated. Here's an overview
of what I do:
- In the report design I create empty label controls (caption=blank) for
each potential column named in a consistant way (label01, label02, etc)
- In the detail section I create unbound textbox controls also named in
a consistant way (Data01, Data02, etc) and set visible=no.
- In the Open event of the report I open a recordset based on a query of
the same base as the crosstab to get a record per crosstab column
- I then do a test to make sure there isn't more records than the no of
columns I presetup in the report design
- Then loop through the recordset and change the Caption of the Labels
and the controlsourse of the Textboxes and make them visible

air code:
i=1
Do Until rs.EOF
Me("Data0" & i).Visible = True
Me("Data0" & i).ControlSource = rs!AATCAT
Me("Label0" & i).Caption = rs!AATCAT
i = i + 1
Loop

Like I said, its a bit complicated and there may be a better way to do
it, but this has worked very well for me so far.

--
Bri


Jan 25 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.