Crosstab report - handling Null fields

  • Thread starter Thread starter NEWER USER
  • Start date Start date
N

NEWER USER

I have a report that is based on a Crosstab query with three column headings
.. The results of the crosstab query are based on selected rows from a multi
list box. If less than three selections are made OR if three selections are
made and one column contains No Values, the report fails and does not open.
I get the message:
"The Microsoft Jet database engine does not recognize " as a valid
field name or expression." I open the report in design view and see the
error message on the field that does not exist. There are over 2500 rows in
my List Box and no way of knowing which contain/do not contain related
records.

How might I go about hiding the column heading/field when null values arise
as the report opens? Any help appreciated.
 
It makes the most sense to continue posting in the same thread rather than
creating a new one. Did you implement the solution I suggested earlier that
creates column aliases?
 
YES I did. I created the alias table with sub report headings which
generated the correct column names and the correct data in fileds A,B,C in
Detail section. As long as there is one row row of data in each column, the
report runs. If I select only one row from my list box, the report errors as
Column B and C on the report can't be found. Select two rows with known data
and C can't be found.

I somwhow need to re-create the report each time I run and only show Columns
ABC when data is present from query. Any thoughts from here?
 
Apparently you might not have set the Column Headings property of the
Crosstab Query. If you look at the SQL of the sample crosstab, you will
notice:

PIVOT tblEmpCustAlias.ColumnAlias In ("A","B","C","D","E","F","G","H");
 
I overlooked that small/HUGE piece of code. Worked perfectly after entering
ABC in Column Headings Properties. Thanks again for all the help. Happy
Holidays!
 
Back
Top