scott.k.fraley@gmail.com wrote:[color=blue]
> ...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[/color]
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
Variable # of columns being output by a Crosstab query 
